package org.jeecgframework.web.service.impl;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.jeecgframework.core.constant.Globals;
import org.jeecgframework.core.util.*;
import org.jeecgframework.web.service.*;
import org.jeecgframework.web.system.pojo.base.*;
import org.jeecgframework.web.system.service.SystemService;
import org.jeecgframework.web.system.service.UserService;
import org.jeecgframework.web.util.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Service("cbsServiceService")
@Transactional
public class CbsServiceServiceImpl extends CommonServiceImpl implements CbsServiceServiceI {

    @Autowired
    ZyCommonService zyCommonService;
    @Autowired
    private UserService userService;
    @Autowired
    SystemService systemService;
    @Autowired
    private CbsServiceAssignListServiceI cbsServiceAssignListService;
    @Autowired
    private CbsEquipmentApplyServiceI cbsEquipmentApplyService;
    @Autowired
    private CbsHandleLogServiceI cbsHandleLogServiceI;
    @Autowired
    private CbsServiceLogServiceI cbsServiceLogServiceI;
    @Autowired
    private WxCommonService wxCommonService;
    @Autowired
    private CbsInspectionEvaluateServiceI cbsInspectionEvaluateServiceI;

    //初始化所有年限,单位（年）
    public final static List<String> ListYear = Arrays.asList("2", "3", "4", "5", "6", "7", "8", "9");

    public <T> void delete(T entity) {
        super.delete(entity);
        //执行删除操作配置的sql增强
        this.doDelSql((CbsServiceEntity) entity);
    }

    public <T> Serializable save(T entity) {
        Serializable t = super.save(entity);
        //执行新增操作配置的sql增强
        this.doAddSql((CbsServiceEntity) entity);
        return t;
    }

    public <T> void saveOrUpdate(T entity) {
        super.saveOrUpdate(entity);
        //执行更新操作配置的sql增强
        this.doUpdateSql((CbsServiceEntity) entity);
    }

    /**
     * 默认按钮-sql增强-新增操作
     *
     * @param
     * @return
     */
    public boolean doAddSql(CbsServiceEntity t) {
        return true;
    }

    /**
     * 默认按钮-sql增强-更新操作
     *
     * @param
     * @return
     */
    public boolean doUpdateSql(CbsServiceEntity t) {
        return true;
    }

    /**
     * 默认按钮-sql增强-删除操作
     *
     * @param
     * @return
     */
    public boolean doDelSql(CbsServiceEntity t) {
        return true;
    }

    @Override
    public AjaxJson sendSMSOfCbsService(String mobilePhone, String htmlText) {
        AjaxJson j = new AjaxJson();
        //发送短信
        Map<String, Object> paraMap = new HashMap<String, Object>();
        //短信模板
        int callback = SendMessage.sendSMSWithContent(mobilePhone, htmlText, systemService);
        if (callback <= 0) {
            j.setMsg("OK");
        } else {
            j.setSuccess(false);
            j.setMsg("ERROR");

        }
        return j;
    }

    /**
     * 服务单信息导出
     *
     * @param request
     * @param response
     */
    public void exportInspectionPlan(HttpServletRequest request, HttpServletResponse response) {
        TSUser user = ResourceUtil.getSessionUserName();
        OutputStream out = null;
        try {
            String fileName = "服务单管理.xlsx";
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-msdownload;");
            response.setHeader("Content-disposition", "attachment; filename=" + (new String(fileName.getBytes("gb2312"), "ISO8859-1")));
            out = response.getOutputStream();
            String[] planIdsArr = oConvertUtils.getString(request.getParameter("planIds")).split(",");
            String cbsServiceIds = "'";
            for (int i = 0; i < planIdsArr.length; i++) {
                if (i == planIdsArr.length - 1) {
                    cbsServiceIds += planIdsArr[i] +"'";
                } else {
                    cbsServiceIds += planIdsArr[i] +"','";
                }
            }
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT ");
            sql.append(" 	cbs.id AS 'id', ");
            sql.append(" 	CASE ");
            sql.append(" 			cbs.STATUS  ");
            sql.append(" 		WHEN '").append(StaticValue.yc).append("' THEN ");
            sql.append(" 			'服务单异常'  ");
            sql.append(" 		WHEN '").append(StaticValue.dcl).append("' THEN ");
            sql.append(" 			'待处理'  ");
            sql.append(" 		WHEN '").append(StaticValue.ysl).append("' THEN ");
            sql.append(" 			'已受理'  ");
            sql.append(" 		WHEN '").append(StaticValue.clz).append("' THEN ");
            sql.append(" 			'处理中'  ");
            sql.append(" 		WHEN '").append(StaticValue.ywc).append("' THEN ");
            sql.append(" 			'已完成'  ");
            sql.append(" 		WHEN '").append(StaticValue.yqx).append("' THEN ");
            sql.append(" 			'已取消'  ");
            sql.append(" 	END AS STATUS, ");
            sql.append(" 	IFNULL( cbs.sn, cbs.sn_server ) AS sn, ");
            sql.append(" 	IFNULL( cbs.CODE, '' ) AS 'code', ");
            sql.append(" 	IFNULL( cbs.sr, '' ) AS 'SR', ");
            sql.append(" 	IFNULL( cbs.so, '' ) AS 'SO', ");
            sql.append(" 	IFNULL( cbs.offer_no, '' ) AS 'offerno', ");
            sql.append(" 	IFNULL( cbs.`zy_cinema_name`, '' ) AS 'names', ");
            sql.append(" 	IFNULL( cbs.address, '' ) AS address, ");
            sql.append(" 	IFNULL( cbs.type_intheater_name, '' ) AS 'intheaterName', ");
            sql.append(" 	IFNULL( cbs.contacts_name, '' ) AS contactsName, ");
            sql.append(" 	IFNULL( cbs.contacts_phone, '' ) AS phone, ");
            sql.append("    cbs.service_type_2 AS service_type_2, ");
            sql.append(" 	IFNULL( cbs.error_code, '' ) AS error_code, ");
            sql.append(" 	IFNULL( cbs.description, '' ) AS description, ");
            sql.append(" 	IFNULL( cbs.judge400, '' ) AS judge400, ");
            sql.append("    CONCAT( IFNULL( e1.NAME, '' ), '>>', IFNULL( e2.NAME, '' ) ) AS error_type, ");
            sql.append(" 	IFNULL( cbs.`level`, '' ) AS `level`, ");
            sql.append("    cbs.person_liable AS person_liable, ");
            sql.append(" 	IFNULL( cbs.create_name, '' ) AS create_name, ");
            sql.append("    cbs.service_provider_code AS service_provider_code, ");
            sql.append("    cbs.engineer_id AS engineer_id, ");
            sql.append("    '' AS engineerphone, ");
            sql.append(" 	CASE ");
            sql.append(" 			cbs.speed_status  ");
            sql.append(" 		WHEN '").append(StaticValue.dfp).append("' THEN ");
            sql.append(" 			'HD待分派'  ");
            sql.append(" 		WHEN '").append(StaticValue.dsfyjj).append("' THEN ");
            sql.append(" 			'第三方已拒绝'  ");
            sql.append(" 		WHEN '").append(StaticValue.tfpdsf).append("' THEN ");
            sql.append(" 			'已分派第三方'  ");
            sql.append(" 		WHEN '").append(StaticValue.dsfyfp).append("' THEN ");
            sql.append(" 			'第三方已分配'  ");
            sql.append(" 		WHEN '").append(StaticValue.qygcsyfp).append("' THEN ");
            sql.append(" 			'区域工程师已分配'  ");
            sql.append(" 		WHEN '").append(StaticValue.gcsyjs).append("' THEN ");
            sql.append(" 			'工程师已接受'  ");
            sql.append(" 		WHEN '").append(StaticValue.gcsssz).append("' THEN ");
            sql.append(" 			'工程师实施中'  ");
            sql.append(" 		WHEN '").append(StaticValue.fwywc).append("' THEN ");
            sql.append(" 			'服务已完成'  ");
            sql.append(" 	END AS SPEEDSTATUS, ");
            sql.append(" 	'无' AS approStatus, ");
            sql.append(" 	CASE ");
            sql.append(" 			cbs.source_type  ");
            sql.append(" 		WHEN '").append(StaticValue.HdType).append("' THEN ");
            sql.append(" 			'HD新建'  ");
            sql.append(" 		WHEN '").append(StaticValue.weixin).append("' THEN ");
            sql.append(" 			'微信'  ");
            sql.append(" 		WHEN '").append(StaticValue.budan).append("' THEN ");
            sql.append(" 			'补单' ELSE ''  ");
            sql.append(" 	END AS sourceType, ");
            sql.append(" 	IFNULL( DATE_FORMAT( cbs.create_date, '%Y-%m-%d %T' ), '' ) AS createDate, ");
            sql.append(" 	IFNULL( DATE_FORMAT( cbs.order_time, '%Y-%m-%d %T' ), '' ) AS orderDate, ");
            sql.append(" 	'' AS arrDate, ");
            sql.append(" 	'' AS arrNum, ");
            sql.append(" 	IFNULL( DATE_FORMAT( cr.finish_time, '%Y-%m-%d %T' ), '' ) AS finishTime, ");
            sql.append(" 	'' AS reportTime, ");
            sql.append(" 	IFNULL( DATE_FORMAT( cbs.close_time, '%Y-%m-%d %T' ), '' ) AS closeTime, ");
            sql.append(" 	'' AS noticeDate, ");
            sql.append(" 	'' AS doa, ");
            sql.append(" 	CASE ");
            sql.append(" 			IFNULL( cr.report_category, '' )  ");
            sql.append(" 		WHEN 1 THEN ");
            sql.append(" 			'否'  ");
            sql.append(" 		WHEN 2 THEN ");
            sql.append(" 			'是' ELSE ''  ");
            sql.append(" 	END AS isremote, ");
            sql.append(" 	CASE ");
            sql.append(" 			IFNULL( cr.repair_front_file, '' )  ");
            sql.append(" 		WHEN '' THEN ");
            sql.append(" 			'无' ELSE '有'  ");
            sql.append(" 	END AS front_file, ");
            sql.append(" 	CASE ");
            sql.append(" 			IFNULL( cr.repair_after_file, '' )  ");
            sql.append(" 		WHEN '' THEN ");
            sql.append(" 			'无' ELSE '有'  ");
            sql.append(" 	END AS after_file, ");
            sql.append(" 	'' AS files, ");
            sql.append(" 	'' AS reportfiles, ");
            sql.append(" 	'' AS refiles, ");
            sql.append(" 	'' AS items, ");
            sql.append(" 	'' AS osn, ");
            sql.append(" 	'' AS oqr, ");
            sql.append(" 	'' AS nsn, ");
            sql.append(" 	'' AS nqr, ");
            sql.append(" 	CASE ");
            sql.append(" 			IFNULL( cr.gzxx, '' )  ");
            sql.append(" 		WHEN '' THEN ");
            sql.append(" 			IFNULL( cr.cljg, '' ) ELSE CONCAT( cr.gzxx, '。', IFNULL( cr.cljg, '' ) )  ");
            sql.append(" 	END AS result, ");
            sql.append(" 	CASE ");
            sql.append(" 			DATE_FORMAT( cbs.create_date, '%Y-%m-%d' ) <= DATE_FORMAT( cbs.repair_end_date, '%Y-%m-%d' )  ");
            sql.append(" 		WHEN TRUE THEN ");
            sql.append(" 			'IW' ELSE 'OW'  ");
            sql.append(" 	END AS ws, ");
            sql.append(" 	CASE ");
            sql.append(" 			IFNULL( cbs.special, '0' )  ");
            sql.append(" 		WHEN 1 THEN ");
            sql.append(" 			'是' ELSE '否'  ");
            sql.append(" 	END AS special  ");
            sql.append(" FROM ");
            sql.append(" 	`cbs_service` cbs ");
            sql.append(" 	LEFT JOIN cbs_scene_report cr ON cr.id = cbs.report_id ");
            sql.append("    LEFT JOIN cbs_error_type e1 ON e1.id = cbs.error_type_1 ");
            sql.append("    LEFT JOIN cbs_error_type e2 ON e2.id = cbs.error_type_2 ");
            sql.append(" WHERE ");
            sql.append(" 	cbs.id IN (").append(cbsServiceIds).append(") ");
            sql.append(" ORDER BY cbs. STATUS ASC, cbs.create_date DESC ");
            List<Map<String, Object>> reusltMaps = super.findForJdbc(sql.toString());
            systemService.addLog(user.getRealName() + "导出" + reusltMaps.size() + "条服务单记录", Globals.Log_Type_OTHER, Globals.Log_Leavel_INFO);
            String codes = "'";
            for(Map<String, Object> m : reusltMaps){
                if (StringUtil.isNotEmpty(m.get("code"))) {
                    codes += m.get("code").toString() + "','";
                }
            }
            codes = codes.substring(0, codes.length() - 2);

            List<Map<String,Object>> userMapList = systemService.findForJdbc("select id, realname from t_s_base_user ");
            List<Map<String,Object>> departMapList = systemService.findForJdbc("select org_code, shortname from t_s_depart ");
            List<Map<String,Object>> engineerMapList = systemService.findForJdbc("select id, NAME, cellphone from zy_engineer ");
            List<Map<String,Object>> serviceTypeMapList = systemService.findForJdbc("select id, name from cbs_service_type");
            List<Map<String,Object>> doaMapList = systemService.findForJdbc("SELECT COUNT(id),service_id as id FROM cbs_equipment_apply WHERE apply_reason = '备件新损' AND apply_reason <> 3 GROUP BY service_id");
            List<Map<String,Object>> arrivalMapList =systemService.findForJdbc("SELECT DATE_FORMAT(MIN(date),'%Y-%m-%d %T') AS arrDate,MAX(num) AS arrNum,service_id FROM arrival_of_abnormal GROUP BY service_id");
            List<Map<String,Object>> filesMapList = systemService.findForJdbc("SELECT GROUP_CONCAT(old_file_name SEPARATOR '\\\\') AS files,business_key FROM zy_file_log WHERE business_type = 'XC5' GROUP BY business_key");
            List<Map<String,Object>> reportMapList = systemService.findForJdbc("SELECT DATE_FORMAT(MIN(create_date),'%Y-%m-%d %T') AS reportTime, cbs_service_id FROM cbs_service_log WHERE log_type = '2' AND description like '%完工报告。'  GROUP BY cbs_service_id");
            List<Map<String,Object>> finishfileMapList = systemService.findForJdbc("select COUNT(id),business_key FROM zy_file_log WHERE business_type = 'XC1' GROUP BY business_key");
            
            sql.setLength(0);
            sql.append(" SELECT ");
            sql.append(" 	cbs_return_equipment.cr_code, ");
            sql.append(" 	GROUP_CONCAT( old_file_name SEPARATOR '\\\\' ) AS files, ");
            sql.append(" 	IFNULL( DATE_FORMAT( MAX( cbs_return_equipment.noticeReturn_time ), '%Y-%m-%d %T' ), '' ) AS noticeDate  ");
            sql.append(" FROM ");
            sql.append(" 	zy_file_log ");
            sql.append(" 	LEFT JOIN cbs_return_equipment_detail ON zy_file_log.business_child_type = cbs_return_equipment_detail.id ");
            sql.append(" 	LEFT JOIN cbs_return_equipment ON cbs_return_equipment.STATUS <> 5  ");
            sql.append(" 	AND cbs_return_equipment.id = cbs_return_equipment_detail.return_equipment_id  ");
            sql.append(" WHERE ");
            sql.append(" 	business_type = 'E'  ");
            sql.append(" 	AND cbs_return_equipment.cr_code IN (").append(codes).append(") ");
            sql.append(" GROUP BY ");
            sql.append(" 	cbs_return_equipment.cr_code ");
            List<Map<String, Object>> refilesMapList = systemService.findForJdbc(sql.toString());
            sql.setLength(0);
            sql.append(" SELECT  cbs_service.id, ");
            sql.append(" 	CASE IFNULL(old_sn,'') WHEN '' THEN '无'  ELSE '有' END AS osn,  ");
            sql.append(" 	CASE IFNULL(old_ercode,'') WHEN '' THEN '无'  ELSE '有' END AS oqr,  ");
            sql.append(" 	CASE IFNULL(cbs_scene_equipment_use.sn,'') WHEN '' THEN '无'  ELSE '有' END AS nsn, ");
            sql.append(" 	CASE IFNULL(equipment_ercode,'') WHEN '' THEN '无'  ELSE '有' END AS nqr,  ");
            sql.append(" 	IFNULL(zy_es_item.item ,'') AS items  ");
            sql.append(" FROM  ");
            sql.append(" 	cbs_service  ");
            sql.append(" LEFT JOIN cbs_scene_equipment_use on (cbs_scene_equipment_use.scene_report_id = cbs_service.report_id)  ");
            sql.append(" LEFT JOIN zy_es_item on cbs_scene_equipment_use.item_id = zy_es_item.id  ");
            sql.append(" WHERE ");
            sql.append(" 	cbs_service.id IN (").append(cbsServiceIds).append(") ");
            List<Map<String, Object>> scenEquipsMapList = systemService.findForJdbc(String.format(sql.toString()));

            
            Map<String, Map> userMap = StringUtil.listToMapWithKey(userMapList, "id");
            Map<String, Map> departMap = StringUtil.listToMapWithKey(departMapList, "org_code");
            Map<String, Map> engineerMap = StringUtil.listToMapWithKey(engineerMapList, "id");
            Map<String, Map> serviceTypeMap = StringUtil.listToMapWithKey(serviceTypeMapList, "id");
            Map<String, Map> refilesMap = StringUtil.listToMapWithKey(refilesMapList, "cr_code");
            Map<String, Map> scenEquipsMap = StringUtil.listToMapWithKey(scenEquipsMapList, "id");
            Map<String, Map> doaMap = StringUtil.listToMapWithKey(doaMapList, "id");
            Map<String, Map> arrivalMap = StringUtil.listToMapWithKey(arrivalMapList, "service_id");
            Map<String, Map> fileMap = StringUtil.listToMapWithKey(filesMapList, "business_key");
            Map<String, Map> reportMap = StringUtil.listToMapWithKey(reportMapList, "cbs_service_id");
            Map<String, Map> finishfileMap = StringUtil.listToMapWithKey(finishfileMapList, "business_key");
            
            InputStream is = ZyInspectionPlanServiceImpl.class.getClassLoader().getResourceAsStream("jeecg/template/cbsServiceList.xlsx");
            XSSFWorkbook xssf = new XSSFWorkbook(is);
            XSSFSheet sheet = xssf.getSheetAt(0);
            int rowNum = 1;
            for (Map<String, Object> dataMap : reusltMaps) {
            	//20190723 add SQL性能优化  Amy start
               	String id = dataMap.get("id").toString();
            	String code = dataMap.get("code").toString();
            	String status = dataMap.get("SPEEDSTATUS").toString();

                if(StringUtil.isNotEmpty(dataMap.get("service_type_2"))){
                    dataMap.put("service_type_2", null != serviceTypeMap.get(dataMap.get("service_type_2").toString()) ? serviceTypeMap.get(dataMap.get("service_type_2").toString()).get("name") : dataMap.get("service_type_2").toString());
                }
                if(StringUtil.isNotEmpty(dataMap.get("person_liable"))){
                    dataMap.put("person_liable", null != userMap.get(dataMap.get("person_liable").toString()) ? userMap.get(dataMap.get("person_liable").toString()).get("realname") : dataMap.get("person_liable").toString());
                }
                if(StringUtil.isNotEmpty(dataMap.get("service_provider_code"))){
                    dataMap.put("service_provider_code", null != departMap.get(dataMap.get("service_provider_code").toString()) ? departMap.get(dataMap.get("service_provider_code").toString()).get("shortname") : dataMap.get("service_provider_code").toString());
                }
                if(StringUtil.isNotEmpty(dataMap.get("engineer_id"))){
                    dataMap.put("engineer_id", null != engineerMap.get(dataMap.get("engineer_id").toString()) ? engineerMap.get(dataMap.get("engineer_id").toString()).get("NAME") : dataMap.get("engineer_id").toString());
                    dataMap.put("engineerphone", null != engineerMap.get(dataMap.get("engineer_id").toString()) ? engineerMap.get(dataMap.get("engineer_id").toString()).get("cellphone") : dataMap.get("engineer_id").toString());
                }
                if (null != refilesMap && refilesMap.get(code) != null) {
            		dataMap.put("refiles", refilesMap.get(code).get("files"));
            		dataMap.put("noticeDate", refilesMap.get(code).get("noticeDate"));
                }
            	
                if (null != arrivalMap && arrivalMap.get(id) != null) {
            		dataMap.put("arrDate", arrivalMap.get(id).get("arrDate"));
            		dataMap.put("arrNum", arrivalMap.get(id).get("arrNum"));
            	 }
            	
                if (null != doaMap && doaMap.get(id) != null) {
                	dataMap.put("doa", '有');
                } else {
            		dataMap.put("doa", '无');
            	}
                
            	String sqltemp = "SELECT CASE `status` WHEN '" + StaticValue.wsp + "' then '未审批' when '" + StaticValue.ytg + "' then '通过' when '" + StaticValue.wtg + "' then '不通过' else '无' end AS approStatus "+
            	"FROM CBS_EQUIPMENT_APPLY WHERE service_id = '"+id+"' ORDER BY create_date DESC";
            	List<Map<String, Object>> reusltMapstemp = super.findForJdbc(sqltemp.toString());
            	if (reusltMapstemp != null && reusltMapstemp.size()>0) {
            		dataMap.put("approStatus", reusltMapstemp.get(0).get("approStatus"));
            	 }
            	
            	if (status.equals("服务已完成")) {
	            	if (null != scenEquipsMap && scenEquipsMap.get(id) != null) {
	            		Map<String, Object> dataMaptemp = scenEquipsMap.get(id);
	            		dataMap.put("osn", dataMaptemp.get("osn"));
	            		dataMap.put("oqr", dataMaptemp.get("oqr"));
	            		dataMap.put("nsn", dataMaptemp.get("nsn"));
	            		dataMap.put("nqr", dataMaptemp.get("nqr"));
	            		dataMap.put("items", dataMaptemp.get("items"));
	            	 }
	            	
	            	if (null != finishfileMap && finishfileMap.get(id) != null) {
	            		dataMap.put("reportfiles", '有');
	            	} else {
	            		dataMap.put("reportfiles", '无');
	            	}
	            	
	            	if (null != fileMap && fileMap.get(id) != null) {
	            		dataMap.put("files", fileMap.get(id).get("files"));
	            	 }
	            	
	            	if (null != reportMap && reportMap.get(id) != null) {
	            		dataMap.put("reportTime", reportMap.get(id).get("reportTime"));
	            	 }
	            		            	
            	}
            	//20190723 add SQL性能优化  Amy end
                XSSFRow row = sheet.createRow(rowNum);
                int cellNum = 0, index = 0;
                for (String key : dataMap.keySet()) {
                    if (index == 0) {
                        index++;
                        continue;
                    }
                    XSSFCell cell = row.createCell(cellNum);
                    cell.setCellValue(dataMap.get(key) != null ? dataMap.get(key).toString() : "");
                    cellNum++;
                }
                rowNum++;
            }
            xssf.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 服务商商务导出服务单
     *
     * @param request
     * @param response
     */
    public void exportInspectionAssignList(HttpServletRequest request, HttpServletResponse response) {
        TSUser user = ResourceUtil.getSessionUserName();
        OutputStream out = null;
        try {
            String fileName = "服务单任务.xlsx";
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-msdownload;");
            response.setHeader("Content-disposition", "attachment; filename=" + (new String(fileName.getBytes("gb2312"), "ISO8859-1")));
            String[] planIdsArr = oConvertUtils.getString(request.getParameter("planIds")).split(",");
            String cbsServiceIds = "'";
            for (int i = 0; i < planIdsArr.length; i++) {
                if (i == planIdsArr.length - 1) {
                    cbsServiceIds += planIdsArr[i] +"'";
                } else {
                    cbsServiceIds += planIdsArr[i] +"','";
                }
            }

            StringBuffer sql = new StringBuffer();
            sql.append("SELECT\n");
            sql.append("	cbs.id AS 'id',\n");
            sql.append("	IFNULL(cbs.sn, cbs.sn_server) AS sn,\n");
            sql.append("	cbs. CODE AS 'code',\n");
            sql.append("	IF (cbs.speed_status = 7, TIMESTAMPDIFF(HOUR,cbs.assign_time,IFNULL(cr.finish_time, cbs.close_time)),\n");
            sql.append(" TIMESTAMPDIFF(HOUR, cbs.assign_time, NOW())\n");
            sql.append(" ) AS TIME,\n");
            sql.append(" cbs.service_type_2, ");
            sql.append(" cbs.`level` AS `level`,\n");
            sql.append(" CASE cbs.speed_status WHEN '" + StaticValue.dfp + "' THEN 'HD待分派' ");
            sql.append(" WHEN '" + StaticValue.dsfyjj + "' THEN '第三方已拒绝' ");
            sql.append(" WHEN '" + StaticValue.tfpdsf + "' THEN '已分派第三方' ");
            sql.append(" WHEN '" + StaticValue.dsfyfp + "' THEN '第三方已分配' ");
            sql.append(" WHEN '" + StaticValue.qygcsyfp + "' THEN '区域工程师已分配' ");
            sql.append(" WHEN '" + StaticValue.gcsyjs + "' THEN '工程师已接受' ");
            sql.append(" WHEN '" + StaticValue.gcsssz + "' THEN '工程师实施中' ");
            sql.append(" WHEN '" + StaticValue.fwywc + "' THEN '服务已完成' END AS speed_status, ");
            sql.append(" cbs.type_intheater_name AS 'typename',\n");
            sql.append(" cbs.zy_cinema_name AS 'intheaterName',\n");
            sql.append(" cbs.province, ");
            sql.append(" cbs.city, ");
            sql.append(" cbs.contacts_name AS contactsName,\n");
            sql.append(" cbs.contacts_phone AS phone,\n");
            sql.append(" DATE_FORMAT(cbs.assign_time,'%Y-%m-%d %H:%i') AS assignTime,\n");
            sql.append(" IFNULL(DATE_FORMAT(IF (cbs.order_time_agin != NULL,cbs.order_time_agin, cbs.order_time),'%Y-%m-%d '),'') AS actualTime,\n");
            sql.append(" IFNULL(DATE_FORMAT(cr.finish_time,'%Y-%m-%d %T'), '')  AS finishTime,\n");
            sql.append(" DATE_FORMAT(cbs.close_time,'%Y-%m-%d %H:%i') AS closeTime,\n");
            sql.append(" '' AS apply,");
            sql.append(" IFNULL(GROUP_CONCAT(d.expecttime SEPARATOR '\\\\'),'') AS expecttime, ");
            sql.append(" IFNULL(cbs.so,'') AS SO, ");
            sql.append(" CASE IFNULL(cr.report_category, '') WHEN 1 THEN '否' WHEN 2 THEN '是' ELSE '' END AS isremote, ");
            sql.append(" cr.memo AS memo, ");
            sql.append(" CASE IFNULL(cr.repair_front_file, '') WHEN '' THEN '无'  ELSE '有' END AS front_file,  ");
            sql.append(" CASE IFNULL(cr.repair_after_file, '') WHEN '' THEN '无'  ELSE '有' END AS after_file,  ");
            sql.append("'' AS files, ");
            sql.append("'' AS reportfiles, ");
            sql.append("'' AS refiles, ");
            sql.append("'' AS items, ");
            sql.append("'' AS osn, ");
            sql.append("'' AS oqr, ");
            sql.append("'' AS nsn, ");
            sql.append("'' AS nqr ");
            sql.append(" FROM\n");
            sql.append("	`cbs_service` cbs\n");
            sql.append(" LEFT JOIN CBS_DELIVER d ON d.cr_code = cbs.code ");
            sql.append(" LEFT JOIN cbs_scene_report cr ON cr.id = cbs.report_id ");
            sql.append(" WHERE ");
            sql.append("	cbs.id IN (").append(cbsServiceIds).append(") ");
            sql.append("GROUP BY cbs.code ORDER BY cbs. speed_status ASC, cbs.create_date DESC ");
            List<Map<String, Object>> reusltMaps = super.findForJdbc(sql.toString());

            String codes = "'";
            for(Map<String, Object> m : reusltMaps){
                if (StringUtil.isNotEmpty(m.get("code"))) {
                    codes += m.get("code").toString() + "','";
                }
            }
            codes = codes.substring(0, codes.length() - 2);

            systemService.addLog(user.getRealName() + "导出" + reusltMaps.size() + "条服务单记录", Globals.Log_Type_OTHER, Globals.Log_Leavel_INFO);

            /** - update by X on 20190802 去掉导出sql中查询的subquery以及多余的关联，采用后续循环判断的方式设置列的值 -*/
            List<Map<String, Object>> equipmentApplyMapList = systemService.findForJdbc("SELECT service_id FROM CBS_EQUIPMENT_APPLY app ");
            List<Map<String,Object>> regioniMapList = systemService.findForJdbc("select id, territoryname from t_s_territory");
            List<Map<String,Object>> serviceTypeMapList = systemService.findForJdbc("select id, name from cbs_service_type");
            List<Map<String,Object>> filesMapList = systemService.findForJdbc("SELECT GROUP_CONCAT(old_file_name SEPARATOR '\\\\') AS files,business_key FROM zy_file_log WHERE business_type = 'XC5' GROUP BY business_key");
            List<Map<String,Object>> finishfileMapList = systemService.findForJdbc("select COUNT(id),business_key FROM zy_file_log WHERE business_type = 'XC1' GROUP BY business_key");
            
            StringBuffer temSql = new StringBuffer();
            temSql.append(" SELECT ");
            temSql.append("     cbs_return_equipment.cr_code, GROUP_CONCAT( old_file_name SEPARATOR '\\\\' ) AS files ");
            temSql.append(" FROM ");
            temSql.append("     zy_file_log ");
            temSql.append(" LEFT JOIN cbs_return_equipment_detail ON zy_file_log.business_child_type = cbs_return_equipment_detail.id ");
            temSql.append(" LEFT JOIN cbs_return_equipment ON cbs_return_equipment.STATUS <> 5 AND cbs_return_equipment.id = cbs_return_equipment_detail.return_equipment_id ");
            temSql.append(" WHERE ");
            temSql.append("     business_type = 'E' ");
            temSql.append(" AND cbs_return_equipment.cr_code in(%s) ");
            temSql.append(" GROUP BY ");
            temSql.append("     cbs_return_equipment.cr_code ");
            List<Map<String,Object>> refilesMapList = systemService.findForJdbc(String.format(temSql.toString(), codes));
            temSql.setLength(0);
            temSql.append(" SELECT ");
            temSql.append("     cbs_service.id, ");
            temSql.append("     CASE IFNULL(old_sn,'') WHEN '' THEN '无'  ELSE '有' END AS osn, ");
            temSql.append("     CASE IFNULL(old_ercode,'') WHEN '' THEN '无'  ELSE '有' END AS oqr, ");
            temSql.append("     CASE IFNULL(cbs_scene_equipment_use.sn,'') WHEN '' THEN '无'  ELSE '有' END AS nsn, ");
            temSql.append("     CASE IFNULL(equipment_ercode,'') WHEN '' THEN '无'  ELSE '有' END AS nqr, ");
            temSql.append("     IFNULL(zy_es_item.item ,'') AS items ");
            temSql.append(" FROM ");
            temSql.append("     cbs_service ");
            temSql.append(" LEFT JOIN cbs_scene_equipment_use on cbs_scene_equipment_use.scene_report_id = cbs_service.report_id ");
            temSql.append(" LEFT JOIN zy_es_item on cbs_scene_equipment_use.item_id = zy_es_item.id ");
            temSql.append(" WHERE ");
            temSql.append("     cbs_service.id IN(%s) ");
            List<Map<String,Object>> scenEquipsMapList = systemService.findForJdbc(String.format(temSql.toString(), cbsServiceIds));

            Map<String, Map> equipmentApplyMap = StringUtil.listToMapWithKey(equipmentApplyMapList, "service_id");
            Map<String, Map> regionMap = StringUtil.listToMapWithKey(regioniMapList, "id");
            Map<String, Map> serviceTypeMap = StringUtil.listToMapWithKey(serviceTypeMapList, "id");
            Map<String, Map> refilesMap = StringUtil.listToMapWithKey(refilesMapList, "cr_code");
            Map<String, Map> scenEquipsMap = StringUtil.listToMapWithKey(scenEquipsMapList, "id");
            Map<String, Map> fileMap = StringUtil.listToMapWithKey(filesMapList, "business_key");
            Map<String, Map> finishfileMap = StringUtil.listToMapWithKey(finishfileMapList, "business_key");
            
            /** - update by X on 20190802 去掉导出sql中查询的subquery以及多余的关联，采用后续循环判断的方式设置列的值 - end */

            InputStream is = ZyInspectionPlanServiceImpl.class.getClassLoader().getResourceAsStream("jeecg/template/cbsFatiliServiceList.xlsx");
            XSSFWorkbook xssf = new XSSFWorkbook(is);
            XSSFSheet sheet = xssf.getSheetAt(0);
            int rowNum = 1;
            for (Map<String, Object> dataMap : reusltMaps) {
                //20190723 add SQL性能优化  Amy start
            	String id = dataMap.get("id").toString();
            	String status = dataMap.get("speed_status").toString();
            	if(StringUtil.isNotEmpty(dataMap.get("id"))){
                    dataMap.put("apply", null != equipmentApplyMap.get(dataMap.get("id").toString()) ? "是" : "否");
                }
            	if(StringUtil.isNotEmpty(dataMap.get("service_type_2"))){
                    dataMap.put("service_type_2", null != serviceTypeMap.get(dataMap.get("service_type_2").toString()) ? serviceTypeMap.get(dataMap.get("service_type_2").toString()).get("name") : dataMap.get("service_type_2").toString());
                }
            	if(StringUtil.isNotEmpty(dataMap.get("province"))){
                    dataMap.put("province", null != regionMap.get(dataMap.get("province").toString()) ? regionMap.get(dataMap.get("province").toString()).get("territoryname") : dataMap.get("province").toString());
                }
            	if(StringUtil.isNotEmpty(dataMap.get("city"))){
                    dataMap.put("city", null != regionMap.get(dataMap.get("city").toString()) ? regionMap.get(dataMap.get("city").toString()).get("territoryname") : dataMap.get("city").toString());
                }

            	if (null != refilesMap ) {
            		dataMap.put("refiles", null != refilesMap.get(dataMap.get("code")) ? refilesMap.get(dataMap.get("code")).get("files") : "");
            	}
            	
            	if (status.equals("服务已完成")) {

	            	if (null != scenEquipsMap && scenEquipsMap.get(id)!= null) {
	            		Map<String, Object> dataMaptemp = scenEquipsMap.get(id);
	            		dataMap.put("osn", dataMaptemp.get("osn"));
	            		dataMap.put("oqr", dataMaptemp.get("oqr"));
	            		dataMap.put("nsn", dataMaptemp.get("nsn"));
	            		dataMap.put("nqr", dataMaptemp.get("nqr"));
	            		dataMap.put("items", dataMaptemp.get("items"));
	            	}

	            	if (null != finishfileMap && finishfileMap.get(id) != null) {
	            		dataMap.put("reportfiles", '有');
	            	} else {
	            		dataMap.put("reportfiles", '无');
	            	}
	            	
	            	if (null != fileMap && fileMap.get(id) != null) {
	            		dataMap.put("files", fileMap.get(id).get("files"));
	            	 }
	            	
            	}
            	//20190723 add SQL性能优化  Amy end
                XSSFRow row = sheet.createRow(rowNum);
                int cellNum = 0, index = 0;
                for (String key : dataMap.keySet()) {
                    if (index == 0) {
                        index++;
                        continue;
                    }
                    XSSFCell cell = row.createCell(cellNum);
                    cell.setCellValue(dataMap.get(key) != null ? dataMap.get(key).toString() : "");
                    cellNum++;
                }
                rowNum++;
            }
            out = response.getOutputStream();
            xssf.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取服务单号
     *
     * @return
     */
    @Override
    public Map getNo() {
        Map m = new HashMap();
        String dateYear = DateUtils.formatDate(new Date(), "yyyy");
        List<String> list = systemService.findListbySql("select MAX(code) as `code` from cbs_service WHERE `code` not LIKE '%-%' AND YEAR(create_date) = YEAR(now()) and `code` LIKE '%CR1%' order by create_date DESC ");
        String codes = "000001";
        if (null != list && list.size() > 0) {
            String b = list.get(0);
            //获取0到6位，判断是否是老数据
//            String bcode = b.substring(0, 6);
            if (null != b && b.length() != 13) {
                int no = Integer.parseInt(b.substring(4, b.length())) + 1;
                if (no < 10) {
                    codes = "00000" + no;
                }
                if (no > 9 && no < 100) {
                    codes = "0000" + no;
                }
                if (no > 99 && no < 1000) {
                    codes = "000" + no;
                }
                if (no > 999 && no < 10000) {
                    codes = "00" + no;
                }
                if (no > 9999 && no < 100000) {
                    codes = "0" + no;
                }
                if (no > 99999) {
                    codes = "" + no;
                }

                m.put("no", "CR" + dateYear.substring(2) + codes);

            } else {
                m.put("no", "CR" + dateYear.substring(2) + codes);
            }

        } else {
            m.put("no", "CR" + dateYear.substring(2) + codes);
        }
        return m;
    }


    /**
     * 获取子单 单号
     *
     * @param parentNo
     * @return
     */
    @Override
    public Map getSublistNo(String parentNo) {
        Map m = new HashMap();
        List<String> list = systemService.findListbySql("select code from cbs_service where code like '%" + parentNo + "-%' order by `code` DESC");
        if (list.size() > 0) {
            String b = list.get(0);
            String[] code = b.split("-");
            System.out.println(Integer.parseInt(code[code.length - 1]));
            if (Integer.parseInt(code[code.length - 1]) + 1 < 10) {
                m.put("code", parentNo + "-0" + (Integer.parseInt(code[code.length - 1]) + 1));
            } else {
                m.put("code", parentNo + "-" + (Integer.parseInt(code[code.length - 1]) + 1));
            }
        } else {
            m.put("code", parentNo + "-01");
        }

        return m;
    }

    /**
     * 工程师接单
     *
     * @param
     */
    @Override
    public void updateCbsOrderTiem(String planIds, String engineerOrderTime) {
        String[] planIdsArr = planIds.split(",");
        for (String id : planIdsArr) {
            CbsServiceEntity cbsServiceEntity = systemService.get(CbsServiceEntity.class, id);
            if (StringUtil.isNotEmpty(cbsServiceEntity)) {
                Date orderTime = DateUtils.str2Date(engineerOrderTime, DateUtils.date_sdf);
                cbsServiceEntity.setSpeedStatus(StaticValue.gcsyjs);
                cbsServiceEntity.setOrderTime(orderTime);  //填写预约时间
                systemService.saveOrUpdate(cbsServiceEntity);
            }
        }
    }

    /**
     * 查找区域工程师，下属工程师
     *
     * @param userId
     */
    @Override
    public List<Map<String, Object>> findEngineerByUserId(String userId, String orgCode) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" bu.id AS id, bu.realname AS realname, ");
        sql.append(" ro.rolecode AS rolename, ro.id AS roid, ");
        sql.append(" dp.id AS dpid, dp.org_code AS CODE ");
        sql.append(" FROM ");
        sql.append(" t_s_base_user bu ");
        sql.append(" LEFT JOIN zy_engineer eg on eg.ts_base_user_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_user_org uo on uo.user_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_depart dp on dp.ID = uo.org_id ");
        sql.append(" LEFT JOIN t_s_role_user ru on ru.userid = bu.ID ");
        sql.append(" LEFT JOIN t_s_role ro on ro.ID = ru.roleid ");
        sql.append(" WHERE ");
        sql.append(" eg.ts_base_user_id = '").append(userId).append("' ");//区域工程师可以分配给自己任务，所以这里根据当前用户（当前登录的是区域工程师）查询出自己
        sql.append(" AND eg.is_delete = '0' ");//只查询未逻辑删除的
        sql.append(" UNION ");
        sql.append(" SELECT ");
        sql.append(" bu.id AS id, bu.realname AS realname, ");
        sql.append(" ro.rolecode AS rolename, ro.id AS roid, ");
        sql.append(" dp.id AS dpid, dp.org_code AS CODE ");
        sql.append(" FROM ");
        sql.append(" t_s_base_user bu ");
        sql.append(" LEFT JOIN zy_engineer eg on eg.ts_base_user_id = bu.ID ");
        sql.append(" LEFT JOIN zy_engineer peg on peg.id = eg.parent_id ");
        sql.append(" LEFT JOIN t_s_user_org uo on uo.user_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_depart dp on dp.ID = uo.org_id ");
        sql.append(" LEFT JOIN t_s_role_user ru on ru.userid = bu.ID ");
        sql.append(" LEFT JOIN t_s_role ro on ro.ID = ru.roleid ");
        sql.append(" WHERE ");
        sql.append(" peg.ts_base_user_id = '").append(userId).append("' ");//父级工程师 的 关联用户表的ID必须是 当前用户（当前登录的是区域工程师）。基础条件
        sql.append(" AND dp.org_code LIKE '").append(orgCode).append("%' ");//组织机构
        sql.append(" AND dp.logic_delete = 0 ");
        sql.append(" AND ( ");
        sql.append(" ro.rolecode = '").append(StaticValue.RULE_CODE_THIRDPARTY_ENGINEER).append("' ");//两种角色都可以指派
        sql.append(" OR ");
        sql.append(" ro.rolecode = '").append(StaticValue.RULE_CODE_AREA_ENGINEER).append("' ");//两种角色都可以指派
        sql.append(" ) ");
        sql.append(" AND eg.state = '").append(StaticValue.DIC_ENGINEER_STATE_ONJOB).append("' ");//在职
        sql.append(" AND eg.is_delete = '0' ");//只查询未逻辑删除的

        return super.findForJdbc(sql.toString());
    }

    @Override
    public List<Map<String, Object>> findEngineerByOrgCodeAndRoleCode(String state, String orgCode) {
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" bu.id AS id, bu.realname AS realname, ");
        sql.append(" ro.rolecode AS rolename, ro.id AS roid, ");
        sql.append(" dp.id AS dpid, dp.org_code AS CODE ");
        sql.append(" FROM  ");
        sql.append(" t_s_base_user bu  ");
        sql.append(" LEFT JOIN zy_engineer eg ON eg.ts_base_user_id = bu.ID  ");
        sql.append(" LEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID  ");
        sql.append(" LEFT JOIN t_s_depart dp ON dp.ID = uo.org_id  ");
        sql.append(" LEFT JOIN t_s_role_user ru ON ru.userid = bu.ID  ");
        sql.append(" LEFT JOIN t_s_role ro ON ro.ID = ru.roleid  ");
        sql.append(" WHERE  ");
        sql.append(" dp.org_code LIKE '").append(orgCode).append("%' ");
        sql.append(" AND dp.logic_delete = 0 ");
        sql.append(" AND ro.rolecode = '").append(state).append("' ");
        sql.append(" AND eg.state = '").append("onJob").append("' "); //在职
        sql.append(" AND eg.is_delete = 0 ");
        rsMapList = findForJdbc(sql.toString());
        return rsMapList;
    }

    @Override
    public List<Map<String, Object>> getUserOrgCode(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        sql.append(" org_code");
        sql.append(" FROM");
        sql.append(" t_s_depart");
        sql.append(" WHERE");
        sql.append(" id IN (");
        sql.append(" SELECT");
        sql.append(" org_id");
        sql.append(" FROM");
        sql.append(" t_s_user_org");
        sql.append(" WHERE");
        sql.append(" user_id = '").append(userId).append("') ");
        return super.findForJdbc(sql.toString());
    }

    //获取所有的服务单记录
    @Override
    public List<Map<String, Object>> getAllService(String sn) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        sb.append("	cbs.id AS id,");
        sb.append("	cbs.`code` AS `code`,");
        sb.append("	t1.`name` AS t1name,");
        sb.append("	t2.`name` AS t2name,");
        sb.append("	e1.`name` AS e1name,");
        sb.append("	e2.`name` AS e2name,");
        sb.append("	CASE cbs.`status`");
        sb.append(" WHEN '-1' THEN");
        sb.append("	'服务单异常'");
        sb.append(" WHEN '1' THEN");
        sb.append("	'待处理'");
        sb.append(" WHEN '2' THEN");
        sb.append("	'已受理'");
        sb.append(" WHEN '3' THEN");
        sb.append("	'处理中'");
        sb.append(" WHEN '4' THEN");
        sb.append("	'已完成'");
        sb.append(" WHEN '5' THEN");
        sb.append("	'已取消'");
        sb.append(" END AS sta,");
        sb.append(" d.shortname AS shortname,");
        sb.append(" CASE cbs.speed_status");
        sb.append(" WHEN '0' THEN \n");
        sb.append("\t' HD待分派' \n");
        sb.append(" WHEN '1' THEN");
        sb.append("	'第三方已拒绝'");
        sb.append(" WHEN '2' THEN");
        sb.append("	'已分派第三方'");
        sb.append(" WHEN '3' THEN");
        sb.append("	'第三方已分配'");
        sb.append(" WHEN '4' THEN");
        sb.append("	'区域工程师已分配'");
        sb.append(" WHEN '5' THEN");
        sb.append("	'工程师已接受'");
        sb.append(" WHEN '6' THEN");
        sb.append("	'工程师实施中'");
        sb.append(" WHEN '7' THEN");
        sb.append("	'服务已完成'");
        sb.append(" END AS speed,");
        sb.append(" DATE_FORMAT(cbs.create_date,'%Y-%m-%d') AS create_date,");
        sb.append(" DATE_FORMAT(cbs.close_time,'%Y-%m-%d') AS close_time,");
        sb.append(" IFNULL(cbs.sn, cbs.sn_server) AS sn");
        sb.append(" FROM");
        sb.append("	`cbs_service` AS cbs");
        sb.append(" LEFT JOIN zy_equipment_spare s ON cbs.ZY_EQUIPMENT_SPARE_ID = s.id");
        sb.append(" LEFT JOIN cbs_service_type AS t1 ON t1.id = cbs.service_type_1");
        sb.append(" LEFT JOIN cbs_service_type AS t2 ON t2.id = cbs.service_type_2");
        sb.append(" LEFT JOIN cbs_error_type AS e1 ON e1.id = cbs.error_type_1");
        sb.append(" LEFT JOIN cbs_error_type AS e2 ON e2.id = cbs.error_type_2");
        sb.append(" LEFT JOIN t_s_depart AS d ON d.org_code = cbs.service_provider_code ");
        sb.append(" WHERE");
        sb.append("	cbs.sn = ? ");
        sb.append("AND cbs.create_date > DATE_SUB(CURDATE(), INTERVAL 12 MONTH)");

        return systemService.findForJdbc(sb.toString(), sn);
    }

    //获取近三个月内的服务单记录
    @Override
    public List<Map<String, Object>> getAllServiceBySn(String sn) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        sb.append("	cbs.id AS id,");
        sb.append("	cbs.`code` AS `code`,");
        sb.append("	t1.`name` AS t1name,");
        sb.append("	t2.`name` AS t2name,");
        sb.append("	e1.`name` AS e1name,");
        sb.append("	e2.`name` AS e2name,");
        sb.append("	CASE cbs.`status`");
        sb.append(" WHEN '-1' THEN");
        sb.append("	'服务单异常'");
        sb.append(" WHEN '1' THEN");
        sb.append("	'待处理'");
        sb.append(" WHEN '2' THEN");
        sb.append("	'已受理'");
        sb.append(" WHEN '3' THEN");
        sb.append("	'处理中'");
        sb.append(" WHEN '4' THEN");
        sb.append("	'已完成'");
        sb.append(" WHEN '5' THEN");
        sb.append("	'已取消'");
        sb.append(" END AS sta,");
        sb.append(" d.shortname AS shortname,");
        sb.append(" CASE cbs.speed_status");
        sb.append(" WHEN '0' THEN \n");
        sb.append("\t' HD待分派' \n");
        sb.append(" WHEN '1' THEN");
        sb.append("	'第三方已拒绝'");
        sb.append(" WHEN '2' THEN");
        sb.append("	'已分派第三方'");
        sb.append(" WHEN '3' THEN");
        sb.append("	'第三方已分配'");
        sb.append(" WHEN '4' THEN");
        sb.append("	'区域工程师已分配'");
        sb.append(" WHEN '5' THEN");
        sb.append("	'工程师已接受'");
        sb.append(" WHEN '6' THEN");
        sb.append("	'工程师实施中'");
        sb.append(" WHEN '7' THEN");
        sb.append("	'服务已完成'");
        sb.append(" END AS speed,");
        sb.append(" DATE_FORMAT(cbs.create_date,'%Y-%m-%d') AS create_date,");
        sb.append(" DATE_FORMAT(cbs.close_time,'%Y-%m-%d') AS close_time,");
        sb.append(" IFNULL(cbs.sn, cbs.sn_server) AS sn");
        sb.append(" FROM");
        sb.append("	`cbs_service` AS cbs");
        sb.append(" LEFT JOIN zy_equipment_spare s ON cbs.ZY_EQUIPMENT_SPARE_ID = s.id");
        sb.append(" LEFT JOIN cbs_service_type AS t1 ON t1.id = cbs.service_type_1");
        sb.append(" LEFT JOIN cbs_service_type AS t2 ON t2.id = cbs.service_type_2");
        sb.append(" LEFT JOIN cbs_error_type AS e1 ON e1.id = cbs.error_type_1");
        sb.append(" LEFT JOIN cbs_error_type AS e2 ON e2.id = cbs.error_type_2");
        sb.append(" LEFT JOIN t_s_depart AS d on d.org_code = cbs.service_provider_code");
        sb.append(" WHERE");
        sb.append("	cbs.sn = ? ");
        sb.append("AND cbs.create_date > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)");

        return systemService.findForJdbc(sb.toString(), sn);
    }

    //获取近三个月内的相关影院的服务单记录
    @Override
    public List<Map<String, Object>> getCinemaList(String sn) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        sb.append("	cbs.id AS id,");
        sb.append("	cbs.`code` AS `code`,");
        sb.append("	t1.`name` AS t1name,");
        sb.append("	t2.`name` AS t2name,");
        sb.append("	e1.`name` AS e1name,");
        sb.append("	e2.`name` AS e2name,");
        sb.append("	CASE cbs.`status`");
        sb.append(" WHEN '-1' THEN");
        sb.append("	'服务单异常'");
        sb.append(" WHEN '1' THEN");
        sb.append("	'待处理'");
        sb.append(" WHEN '2' THEN");
        sb.append("	'已受理'");
        sb.append(" WHEN '3' THEN");
        sb.append("	'处理中'");
        sb.append(" WHEN '4' THEN");
        sb.append("	'已完成'");
        sb.append(" WHEN '5' THEN");
        sb.append("	'已取消'");
        sb.append(" END AS sta,");
        sb.append(" d.shortname AS shortname,");
        sb.append(" CASE cbs.speed_status");
        sb.append(" WHEN '0' THEN \n");
        sb.append("\t' HD待分派' \n");
        sb.append(" WHEN '1' THEN");
        sb.append("	'第三方已拒绝'");
        sb.append(" WHEN '2' THEN");
        sb.append("	'已分派第三方'");
        sb.append(" WHEN '3' THEN");
        sb.append("	'第三方已分配'");
        sb.append(" WHEN '4' THEN");
        sb.append("	'区域工程师已分配'");
        sb.append(" WHEN '5' THEN");
        sb.append("	'工程师已接受'");
        sb.append(" WHEN '6' THEN");
        sb.append("	'工程师实施中'");
        sb.append(" WHEN '7' THEN");
        sb.append("	'服务已完成'");
        sb.append(" END AS speed,");
        sb.append(" DATE_FORMAT(cbs.create_date,'%Y-%m-%d') AS create_date,");
        sb.append(" DATE_FORMAT(cbs.close_time,'%Y-%m-%d') AS close_time,");
        sb.append(" IFNULL(cbs.sn, cbs.sn_server) AS sn");
        sb.append(" FROM");
        sb.append("	`zy_cinema` AS c");
        sb.append(" LEFT JOIN cbs_service as cbs ON c.`name` = cbs.zy_cinema_name");
        sb.append(" LEFT JOIN cbs_service_type AS t1 ON t1.id = cbs.service_type_1");
        sb.append(" LEFT JOIN cbs_service_type AS t2 ON t2.id = cbs.service_type_2");
        sb.append(" LEFT JOIN cbs_error_type AS e1 ON e1.id = cbs.error_type_1");
        sb.append(" LEFT JOIN cbs_error_type AS e2 ON e2.id = cbs.error_type_2");
        sb.append(" LEFT JOIN t_s_depart AS d ON d.org_code = cbs.service_provider_code");
        sb.append(" WHERE");
        sb.append("	cbs.zy_cinema_name = (\n");
        sb.append("SELECT\n" +
                "\t\t\tc.`name`\n" +
                "\t\tFROM\n" +
                "\t\t\tzy_equipment_spare s\n" +
                "\t\tLEFT JOIN zy_cinema c ON s.belong_cinema_id = c.id\n" +
                "\t\tWHERE\n" +
                "\t\t\ts.sn = ? \n" +
                "\t\tLIMIT 1");
        sb.append("\t) ");
        sb.append("AND cbs.create_date > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)");

        return systemService.findForJdbc(sb.toString(), sn);
    }

    @Override
    public List<Map<String, Object>> getAllPlanBySn(String sn) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT");
        sb.append("	p.id AS id,");
        sb.append("	p.plan_id AS planIds,");
        sb.append("	CASE p.state");
        sb.append(" WHEN 'a_todo' THEN");
        sb.append("	'待处理'");
        sb.append(" WHEN 'b_sended_to_thirdParty' THEN");
        sb.append("	'已分配到第三方'");
        sb.append(" WHEN 'c_third_party_refuse' THEN");
        sb.append("	'第三方拒绝'");
        sb.append(" WHEN 'd_third_party_assigned' THEN");
        sb.append("	'第三方分配'");
        sb.append(" WHEN 'e_engineer_refused' THEN");
        sb.append("	'工程师拒绝'");
        sb.append(" WHEN 'f_engineer_assigned' THEN");
        sb.append("	'工程师接受'");
        sb.append(" WHEN 'g_engineer_in_implement' THEN");
        sb.append("	'工程师实施中'");
        sb.append(" WHEN 'h_aeg_refused' THEN");
        sb.append("	'区域工程师拒绝'");
        sb.append(" WHEN 'i_aeg_assigned' THEN");
        sb.append("	'区域工程师已分配'");
        sb.append(" WHEN 'j_inspection_complete' THEN");
        sb.append("	'已完成巡检计划'");
        sb.append(" WHEN 'k_inspection_cancel' THEN");
        sb.append("	'巡检计划已取消'");
        sb.append(" END AS state,");
        sb.append("  DATE_FORMAT(p.ip_plan_date,'%Y-%m-%d') AS dates,");
        sb.append("  DATE_FORMAT(p.engineer_order_time,'%Y-%m-%d') AS time,");
        sb.append("  d.shortname AS shortname,");
        sb.append("  e.`name` AS eName,");
        sb.append("  DATE_FORMAT(r.INP_END_TIME,'%Y-%m-%d') AS beginTime");
        sb.append(" FROM");
        sb.append("	zy_equipment_spare AS s");
        sb.append(" LEFT JOIN zy_inspection_plan AS p ON p.equipment_spare_id = s.id");
        sb.append(" LEFT JOIN zy_inspection_report AS r ON r.INSPECTION_PLAN_ID = p.id");
        sb.append(" LEFT JOIN t_s_depart AS d ON d.org_code = p.company");
        sb.append(" LEFT JOIN zy_engineer AS e ON e.id = p.engineer");
        sb.append(" WHERE");
        sb.append(" p.id is not NULL and ");
        sb.append("	s.sn = ?");
//        sb.append("AND cbs.create_date > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)");

        return systemService.findForJdbc(sb.toString(), sn);
    }

    @Override
    public List<Map<String, Object>> getCbsServiceByCid(String cid) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tcbs.contacts_name AS `name`,\n" +
                "\tcbs.email AS email,\n" +
                "\tcbs.qq AS qq,\n" +
                "\tcbs.address AS address,\n" +
                "\tcbs.city AS city,\n" +
                "\tcbs.province AS province,\n" +
                "\tcbs.case_flag AS case_flag,\n" +
                "\tcbs.client AS client,\n" +
                "\tcbs.`code` AS `code`,\n" +
                "\tcbs.description AS description,\n" +
                "\tcbs.hd_close AS hd_close,\n" +
                "\tcbs.judge400 AS judge400,\n" +
                "\tcbs.`level` AS `level`,\n" +
                "\tcbs.source_type AS sourceType,\n" +
                "\tcbs.error_code AS errorCode,\n" +
                "\tcbs.description AS description,\n" +
                "\tcbs.special AS special,\n" +
                "\tcbs.hd_close AS hdClose,\n" +
                "\tcbs.error_type_1 AS error_type_1,\n" +
                "\tcbs.error_type_2 AS error_type_2,\n" +
                "\tcbs.service_type_1 AS service_type_1,\n" +
                "\tcbs.service_type_2 AS service_type_2,\n" +
                "\tcbs.equipment_error_type_1 AS equipment_error_type_1,\n" +
                "\tcbs.equipment_error_type_2 AS equipment_error_type_2,\n" +
                "\tcbs.CONTACTS_PHONE AS contactsphone,\n" +
                "\tcbs.sn AS sn,\n" +
                "\tcbs.sn_server AS snService,\n" +
                "\tcbs.id AS cid,\n" +
                "\tcbs.`code` AS `code`,\n" +
                "\tcbs.`status` AS sta,\n" +
                "\tcbs.`HANDLE_DES` AS handledes,\n" +
                "\td.id AS shortname,\n" +
                "\tCASE cbs.speed_status\n" +
                "WHEN '0' THEN\n" +
                "\t'HD待分配'\n" +
                "WHEN '1' THEN\n" +
                "\t'第三方已拒绝'\n" +
                "WHEN '2' THEN\n" +
                "\t'已分派第三方'\n" +
                "WHEN '3' THEN\n" +
                "\t'第三方已分配'\n" +
                "WHEN '4' THEN\n" +
                "\t'区域工程师已分配'\n" +
                "WHEN '5' THEN\n" +
                "\t'工程师已接受'\n" +
                "WHEN '6' THEN\n" +
                "\t'工程师实施中'\n" +
                "WHEN '7' THEN\n" +
                "\t'服务已完成'\n" +
                "END AS speed,\n" +
                " DATE_FORMAT(cbs.create_date, '%Y-%m-%d') AS create_date,\n" +
                " DATE_FORMAT(cbs.close_time, '%Y-%m-%d') AS close_time,\n" +
                " ci.`id` AS cinameid,\n" +
                " ifNULL(cbs.zy_cinema_name,'') AS ciname,\n" +
                " s.bp_no AS bpno,\n" +
                " sp1.bp_no AS bpnoService,\n" +
                " t6.type_desc AS typedescService,\n" +
                " t4.type_desc AS typedesc1Service,\n" +
                " CASE tp_2.typecode\n" +
                "WHEN '1001' THEN\n" +
                "	'使用中'\n" +
                "WHEN '1002' THEN\n" +
                "	'未使用'\n" +
                "WHEN '1003' THEN\n" +
                "	'停止使用'\n" +
                "END AS servicestateService,\n" +
                " CASE sp1.is_normal\n" +
                "WHEN '1' THEN\n" +
                "	'\n" +
                "是'\n" +
                "ELSE\n" +
                "	'否'\n" +
                "END AS is_normalSerrvice,\n" +
                " sp1.exception_reason AS exceptionreasonService,\n" +
                " IFNULL(\n" +
                "\tDATE_FORMAT(\n" +
                "\t\tsp1.install_date,\n" +
                "\t\t'%Y-%m-%d'\n" +
                "\t),\n" +
                "\t'无'\n" +
                ") AS install_dateService,\n" +
                " sp1.remarks AS remarksService,\n" +
                "\n" +
                "CASE\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\tsp1.repair_end_date\n" +
                ") < 0 THEN\n" +
                "\t'保外'\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\tsp1.repair_end_date\n" +
                ") > 0 THEN\n" +
                "\t'保内'\n" +
                "ELSE\n" +
                "\t''\n" +
                "END AS inReparirDateValService,\n" +
                " IFNULL(\n" +
                "\tDATE_FORMAT(\n" +
                "\t\tsp1.warranty_begin_date,\n" +
                "\t\t'%Y-%m-%d'\n" +
                "\t),\n" +
                "\t'无'\n" +
                ") AS warrantyBeginDateService,\n" +
                " IFNULL(\n" +
                "\tDATE_FORMAT(\n" +
                "\t\tsp1.repair_end_date,\n" +
                "\t\t'%Y-%m-%d'\n" +
                "\t),\n" +
                "\t'无'\n" +
                ") AS repairEndDateService,\n" +
                " ci.address AS ciaddress,\n" +
                " t3.type_desc AS typedesc,\n" +
                " t1.type_desc AS typedesc1,\n" +
                " CASE tp_1.typecode\n" +
                "WHEN '1001' THEN\n" +
                "	'使用中'\n" +
                "WHEN '1002' THEN\n" +
                "	'未使用'\n" +
                "WHEN '1003' THEN\n" +
                "	'停止使用'\n" +
                "END AS servicestate,\n" +
                " IFNULL(\n" +
                "\tDATE_FORMAT(s.install_date, '%Y-%m-%d'),\n" +
                "\t'无'\n" +
                ") AS install_date,\n" +
                " CASE s.is_normal\n" +
                "WHEN '1' THEN\n" +
                "	'\n" +
                "是'\n" +
                "ELSE\n" +
                "	'否'\n" +
                "END AS is_normal,\n" +
                " s.exception_reason AS exceptionreason,\n" +
                "\n" +
                "CASE\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\ts.repair_end_date\n" +
                ") < 0 THEN\n" +
                "\t'保外'\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\ts.repair_end_date\n" +
                ") > 0 THEN\n" +
                "\t'保内'\n" +
                "ELSE\n" +
                "\t''\n" +
                "END AS inReparirDateVal,\n" +
                " s.remarks AS remarks,\n" +
                " IFNULL(\n" +
                "\tDATE_FORMAT(\n" +
                "\t\ts.warranty_begin_date,\n" +
                "\t\t'%Y-%m-%d'\n" +
                "\t),\n" +
                "\t'无'\n" +
                ") AS warrantyBeginDate,\n" +
                " IFNULL(\n" +
                "\tDATE_FORMAT(\n" +
                "\t\ts.repair_end_date,\n" +
                "\t\t'%Y-%m-%d'\n" +
                "\t),\n" +
                "\t'无'\n" +
                ") AS repairEndDate,\n" +
                " cbs.type_intheater_name AS typename,\n" +
                " d.shortname as shortname,\n" +
                " d1.shortname as shortnameService," +
                " ifnull(s.id,'') as sid," +
                " ifnull(sp1.id,'') as sp1id," +
                " CASE s.laser_reconstruction_type\n" +
                "WHEN '1' THEN\n" +
                "\t'未光改'\n" +
                "WHEN '2' THEN\n" +
                "\t'光峰光改'\n" +
                "WHEN '3' THEN\n" +
                "\t'巴可光改'\n" +
                "WHEN '4' THEN\n" +
                "\t'其他光改'\n" +
                "END AS laserreconstructiontype,\n" +
                "  cbs.service_provider_code as serviceProviderCode," +
                "con.repair_provider AS repairProvider,\n" +
                "CASE u.authenticationlevel\n" +
                "WHEN '1' THEN\n" +
                "\t'银牌'\n" +
                "WHEN '2' THEN\n" +
                "\t'钻石'\n" +
                "WHEN '3' THEN\n" +
                "\t'待审批'\n" +
                "WHEN '4' THEN\n" +
                "\t'金牌'\n" +
                "ELSE\n" +
                "\t'非微信'\n" +
                "END AS authenticationlevel," +
                "IFNULL(\n" +
                "\tDATE_FORMAT(MAX(sc.create_date), '%Y-%m-%d'),\n" +
                "\t''\n" +
                ") AS vcreate_date,\n" +
                " MAX(IFNULL(sc.REPAIR_AFTER1,'')) AS v1,\n" +
                " MAX(IFNULL(sc.REPAIR_AFTER2,'')) AS v2, \n " +
                " MAX(IFNULL(cbs.authentication,'')) AS authentication, \n " +
                "IFNULL(\n" +
                "\tDATE_FORMAT(MAX(sc1.create_date), '%Y-%m-%d'),\n" +
                "\t''\n" +
                ") AS vcreate_dateService,\n" +
                " MAX(IFNULL(sc1.REPAIR_AFTER1,'')) AS v1Service,\n" +
                " MAX(IFNULL(sc1.REPAIR_AFTER2,'')) AS v2Service, \n " +
                " IFNULL(esitem.item_desc, '') AS esitemDesc\n" +
                "FROM\n" +
                "\tcbs_service cbs\n" +
                "LEFT JOIN zy_contacts zc ON zc.id = cbs.zy_contacts_id\n" +
                "LEFT JOIN t_s_base_user u ON u.id = zc.base_id\n" +
                "LEFT JOIN zy_equipment_spare s ON cbs.sn = s.sn\n" +
                "LEFT JOIN zy_es_item esitem ON esitem.item = s.item\n" +
                "LEFT JOIN zy_equipment_spare sp1 ON sp1.sn = cbs.sn_server\n" +
                "LEFT JOIN t_s_depart AS d ON d.bp_code = s.bp_no\n" +
                "LEFT JOIN t_s_type tp_1 ON tp_1.typecode = s.server_state\n" +
                "LEFT JOIN zy_equipment_type t1 ON t1.id = s.type\n" +
                "LEFT JOIN zy_equipment_type t2 ON t2.id = t1.parent_id\n" +
                "LEFT JOIN zy_equipment_type t3 ON t3.id = t2.parent_id\n" +
                "LEFT JOIN zy_cinema ci ON ci.id = cbs.zy_cinema_id\n" +
                "LEFT JOIN t_s_depart AS d1 ON d1.bp_code = sp1.bp_no\n" +
                "LEFT JOIN t_s_type tp_2 ON tp_2.typecode = sp1.server_state\n" +
                "LEFT JOIN zy_equipment_type t4 ON t4.id = sp1.type\n" +
                "LEFT JOIN zy_equipment_type t5 ON t5.id = t4.parent_id\n" +
                "LEFT JOIN zy_equipment_type t6 ON t6.id = t5.parent_id\n" +
                "LEFT JOIN CBS_CONTRACT_MC_EQUIPMENT mc ON mc.sn = cbs.sn\n" +
                "LEFT JOIN CBS_CONTRACT con ON con.contract_code = mc.contract_code\n" +
                "LEFT JOIN CBS_SCENE_REPORT sc ON sc.sn = cbs.sn \n" +
                "LEFT JOIN CBS_SCENE_REPORT sc1 ON sc1.sn = sp1.sn \n");
        sb.append("WHERE ");
        sb.append("cbs.id = ? LIMIT 1");
        return systemService.findForJdbc(sb.toString(), cid);
    }

    @Override
    public List<Map<String, Object>> getAllSLABySn(String cid) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tIFNULL(\n" +
                "\t\tMAX(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN s.service_name LIKE '%年度巡检%' THEN\n" +
                "\t\t\t\tsla.s_Item\n" +
                "\t\t\tEND\n" +
                "\t\t),\n" +
                "\t\t''\n" +
                "\t) AS s1,\n" +
                "\tIFNULL(\n" +
                "\t\tMAX(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN s.service_name LIKE '%工程师级别要求%' THEN\n" +
                "\t\t\t\tsla.s_Item\n" +
                "\t\t\tEND\n" +
                "\t\t),\n" +
                "\t\t''\n" +
                "\t) AS s2,\n" +
                "\tIFNULL(\n" +
                "\t\tMAX(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN s.service_name LIKE '%巡检光路%' THEN\n" +
                "\t\t\t\tsla.s_Item\n" +
                "\t\t\tEND\n" +
                "\t\t),\n" +
                "\t\t''\n" +
                "\t) AS s3,\n" +
                "\tIFNULL(\n" +
                "\t\tMAX(\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN s.service_name LIKE '%硬盘维修%' THEN\n" +
                "\t\t\t\tsla.s_Item\n" +
                "\t\t\tEND\n" +
                "\t\t),\n" +
                "\t\t''\n" +
                "\t) AS s4,\n" +
                "\tIFNULL(\n" +
                "\t\tmax(c.SERVICE_COMMITMENT),\n" +
                "\t\t''\n" +
                "\t) AS SERVICECOMMITMENT,\n" +
                "\tIFNULL(\n" +
                "\t\tDATE_FORMAT(\n" +
                "\t\t\tspare.REPAIR_END_DATE,\n" +
                "\t\t\t'%Y-%m-%d'\n" +
                "\t\t),\n" +
                "\t\t''\n" +
                "\t) AS REPAIR_END_DATE,\n" +
                "\tIFNULL(\n" +
                "\t\tDATE_FORMAT(\n" +
                "\t\t\tspare.warranty_begin_date,\n" +
                "\t\t\t'%Y-%m-%d'\n" +
                "\t\t),\n" +
                "\t\t''\n" +
                "\t) AS warranty_begin_date,\n" +
                "\tIFNULL(d.shortname, '') AS shortname,\n" +
                "\tIFNULL(\n" +
                "\t\tCASE c.repair_type\n" +
                "\t\tWHEN '1' THEN\n" +
                "\t\t\t'推荐'\n" +
                "\t\tWHEN '2' THEN\n" +
                "\t\t\t'指定'\n" +
                "\t\tEND,\n" +
                "\t\t''\n" +
                "\t) AS repairType,\n" +
                "\t IFNULL(c.supplement,'') as supplement\n" +
                "FROM\n" +
                "\tCBS_CONTRACT_MC_EQUIPMENT e\n" +
                "LEFT JOIN CBS_CONTRACT c ON c.contract_code = e.contract_code\n" +
                "LEFT JOIN sla_contract sla ON sla.CONTRACT_ID = c.id\n" +
                "LEFT JOIN CBS_CONTRACT_SLA s ON s.id = sla.sla_id\n" +
                "LEFT JOIN zy_equipment_spare spare ON spare.sn = e.sn\n" +
                "LEFT JOIN t_s_depart d ON d.org_code = c.REPAIR_PROVIDER\n" +
                "WHERE\n" +
                "e.sn = ?\n");
        return systemService.findForJdbc(sb.toString(), cid);
    }

    /**
     * 根据服务类型id获取服务类型名称
     *
     * @param serviceId
     * @return
     */
    @Override
    public String getServiceTypeName(String serviceId) {
        CbsServiceTypeEntity cbsServiceTypeEntity = null;
        if (StringUtil.isNotEmpty(serviceId)) {
            cbsServiceTypeEntity = systemService.get(CbsServiceTypeEntity.class, serviceId);
        } else {
            throw new NullPointerException("服务类型id不能为空！");
        }
        return cbsServiceTypeEntity != null ? cbsServiceTypeEntity.getName() : "";
    }

    @Override
    public List<Map<String, Object>> getgetProjector(String sn) {
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT\n" +
                "\ts.id AS id,\n" +
                "\tIFNULL(eq.type_desc, '') AS type,\n" +
                "\tIFNULL(eq2.type_desc, '') AS type_desc,\n" +
                "\tIFNULL(s.bp_no, '') AS BpNo,\n" +
                "\tCASE s.laser_reconstruction\n" +
                "WHEN '1' THEN\n" +
                "\t'是'\n" +
                "ELSE\n" +
                "\t'否'\n" +
                "END AS laser,\n" +
                " CASE s.server_state\n" +
                "WHEN '1001' THEN\n" +
                "\t'使用中'\n" +
                "WHEN '1002' THEN\n" +
                "\t'未使用'\n" +
                "WHEN '1003' THEN\n" +
                "\t'停止使用'\n" +
                "ELSE\n" +
                "\t''\n" +
                "END AS serviceState,\n" +
                "CASE\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\ts.repair_end_date\n" +
                ") < 0 THEN\n" +
                "\t'保外'\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\ts.repair_end_date\n" +
                ") > 0 THEN\n" +
                "\t'保内'\n" +
                "ELSE\n" +
                "\t''\n" +
                "END AS inReparirDateVal,\n" +
                " IFNULL(\n" +
                "\tDATE_FORMAT(s.INSTALL_DATE, '%Y-%m-%d'),\n" +
                "\t'无'\n" +
                ") AS INSTALL_DATE,\n" +
                " IFNULL(\n" +
                "\tDATE_FORMAT(\n" +
                "\t\ts.warranty_begin_date,\n" +
                "\t\t'%Y-%m-%d'\n" +
                "\t),\n" +
                "\t'无'\n" +
                ") AS warrantyBeginDate,\n" +
                " IFNULL(\n" +
                "\tDATE_FORMAT(\n" +
                "\t\ts.REPAIR_END_DATE,\n" +
                "\t\t'%Y-%m-%d'\n" +
                "\t),\n" +
                "\t'无'\n" +
                ") AS REPAIR_END_DATE,\n" +
                " CASE s.is_normal\n" +
                "WHEN '1' THEN\n" +
                "\t'是'\n" +
                "ELSE\n" +
                "\t'否'\n" +
                "END AS isNormal,\n" +
                " IFNULL(s.EXCEPTION_REASON, '') AS exceptionReason,\n" +
                " IFNULL(s.remarks, '') AS remarks,\n" +
                " CASE s.laser_reconstruction_type\n" +
                "WHEN '1' THEN\n" +
                "\t'未光改'\n" +
                "WHEN '2' THEN\n" +
                "\t'光峰光改'\n" +
                "WHEN '3' THEN\n" +
                "\t'巴可光改'\n" +
                "WHEN '4' THEN\n" +
                "\t'其他光改'\n" +
                "ELSE\n" +
                "\t''\n" +
                "END AS laserreconstructiontype,\n" +
                " IFNULL(d.shortname, '') AS shortname,\n" +
                " IFNULL(\n" +
                "\tDATE_FORMAT(\n" +
                "\t\tMAX(sc.create_date),\n" +
                "\t\t'%Y-%m-%d'\n" +
                "\t),\n" +
                "\t''\n" +
                ") AS vcreate_date,\n" +
                " IFNULL(MAX(IFNULL(sc.REPAIR_AFTER1, '')), '') AS v1,\n" +
                " IFNULL(MAX(IFNULL(sc.REPAIR_AFTER2, '')), '') AS v2,\n" +
                " IFNULL(esitem.item_desc, '') AS esitemDesc\n" +
                "FROM\n" +
                "\tzy_equipment_spare s\n" +
                "LEFT JOIN zy_equipment_type eq ON s.type = eq.id\n" +
                "LEFT JOIN zy_equipment_type eq1 ON eq.parent_id = eq1.id\n" +
                "LEFT JOIN zy_equipment_type eq2 ON eq1.parent_id = eq2.id\n" +
                "LEFT JOIN zy_es_item esitem ON esitem.item = s.item\n" +
                "LEFT JOIN t_s_depart AS d ON d.bp_code = s.bp_no and s.bp_no <> '' \n" +
                "LEFT JOIN CBS_SCENE_REPORT sc ON sc.sn = s.sn\n" +
                "WHERE\n");
        sf.append("	s.sn = ?");
        return systemService.findForJdbc(sf.toString(), sn);
    }

    public List<Map<String, Object>> getCinemaBySn(String sn) {
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT\n");
        sf.append("	c.id AS zyCinemaIds,\n");
        sf.append("	c.`name` AS zyCinemaName,\n");
        sf.append("	t1.ID AS zyCinemaId,\n");
        sf.append("	t1.territoryparentid as zyCinemaProvince,\n");
        sf.append("	c.province AS zyCinemaCity,\n");
        sf.append("	cinema,\n");
        sf.append("	c.address AS zyCinemaAddress\n");
        sf.append("FROM\n");
        sf.append("	zy_equipment_spare s\n");
        sf.append("LEFT JOIN zy_cinema c ON c.id = s.belong_cinema_id\n");
        sf.append("LEFT JOIN t_s_territory t ON t.id = c.province\n");
        sf.append("LEFT JOIN t_s_territory t1 ON t1.id = t.ID\n");
        sf.append("WHERE\n");
        sf.append("	s.sn = ?");
        return systemService.findForJdbc(sf.toString(), sn);
    }

    /**
     * 判断编辑的单子是否是投诉子单，且父单是否是投诉子单，且除本单外是否父类下还有投诉子单
     *
     * @param ServiceId
     * @return
     */
    @Override
    public boolean getServiceTypeNameByServiceId(String ServiceId) {
        boolean flag = true;
        CbsServiceEntity cbsServiceEntity = systemService.get(CbsServiceEntity.class, ServiceId);
        String[] type = cbsServiceEntity.getCode().split("-");
        if (type.length > 1) {
            //是子单
            List<Map<String, Object>> list = this.getServiceType(ServiceId);
            for (Map<String, Object> stringObjectMap : list) {
                if (stringObjectMap.get("name").equals("投诉")) {
                    /*编辑的单子是投诉子单，判断父类是否是投诉子单*/
                    List<Map<String, Object>> list1 = this.getServiceType(type[0]);
                    for (Map<String, Object> objectMap : list1) {
                        if (objectMap.get("name").equals("投诉")) {
                            //父类是投诉子单,判断父类下是否含有除本单外的投诉子单
                            List<Map<String, Object>> list2 = getParentServiceTerm(objectMap.get("code").toString(), ServiceId, type[0]);
                            if (list2.size() == 0) {
                                flag = false;
                            }
                        } else {
                            flag = false;
                        }
                    }
                } else {
                    flag = false;  //不是投诉子单
                }
            }
        } else {
            flag = false;  //不是子单
        }


        return flag;
    }

    @Override
    public List<Map<String, Object>> getContactsById(String cbsId) {
        StringBuffer sf = new StringBuffer("SELECT\n" +
                "\tIFNULL(d.shortname,'') AS shortname,\n" +
                "\tIFNULL(l.assign_des,'') AS assignDes,\n" +
                "\tIFNULL(\n" +
                "\t\tconcat(\n" +
                "\n" +
                "\t\t\tIF (\n" +
                "\t\t\t\ts.`SPEED_STATUS` = " + StaticValue.fwywc + ",\n" +
                "\t\t\t\tTIMESTAMPDIFF(\n" +
                "\t\t\t\t\tHOUR,\n" +
                "\t\t\t\t\ts.assign_time,\n" +
                "\t\t\t\t\ts.close_time\n" +
                "\t\t\t\t),\n" +
                "\t\t\t\tTIMESTAMPDIFF(HOUR, s.assign_time, NOW())\n" +
                "\t\t\t),\n" +
                "\t\t\t'小时'\n" +
                "\t\t),\n" +
                "\t\t''\n" +
                "\t) AS dealWithTiem,\n" +
                "IFNULL(u.realname,'') as realname,\n" +
                "IFNULL(eg.authentication,'') AS authentication\n" +
                "FROM\n" +
                "\t`cbs_service_assign_list` l\n" +
                "LEFT JOIN cbs_service s ON s.id = l.service_id\n" +
                "LEFT JOIN t_s_depart d ON d.org_code = s.service_provider_code\n" +
                "LEFT JOIN cbs_service_assign_list l1 ON l1.service_id = s.id\n" +
                "AND l1.assign_torole = 3\n" +
                "AND l.`status` != 1\n" +
                "AND l.`status` != 2\n" +
                "LEFT JOIN t_s_base_user u ON u.id=l1.assign_touser \n" +
                "LEFT JOIN zy_engineer eg ON eg.ts_base_user_id = u.ID AND eg.state ='onJob' AND eg.is_delete = 0\n" +
                "WHERE\n" +
                "\tl.service_id = '" + cbsId + "'\n" +
                "AND l.`status` != 1\n" +
                "AND l.`status` != 2\n" +
                "ORDER BY\n" +
                "\tl.create_date DESC\n" +
                "LIMIT 1");
        return systemService.findForJdbc(sf.toString());
    }

    @Override
    public Map<String, Object> queryAppIpTpList(String TodoNum, String styletype, int type, String area, String planYear, String planMonth, String esSn, String cmId, int counters, int pageSize) {
        Map<String, Object> rsMap = new HashMap<String, Object>();
        TSUser user = ResourceUtil.getSessionUserName();  //当前登录人

        //服务单内容
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT\n");
        sf.append("\tcbs.id AS id,\n");
        sf.append("\tCOUNT(cbs.id) AS total,\n");
        sf.append("\tIFNULL(cbs.`zy_cinema_name`, '') AS `name`,\n");
        sf.append("\tIFNULL(cbs.`door_number`, '0') AS `doornumber`,\n");
        sf.append("\tIFNULL(cbs.`level`, '') AS `level`,\n");
        sf.append("\tIFNULL(cbs.address, '') AS address,\n");
        sf.append("\tIFNULL(cbs.contacts_name, '') AS contactsname,\n");
        sf.append("\tIFNULL(cbs.contacts_phone, '') AS cellphone1,\n");
        sf.append("\tIFNULL('', '') AS cellphone2,\n");
        sf.append("\tIFNULL(cbs.description, '') AS description,\n");
        sf.append("\tIFNULL(cbs.type_dec, '') AS type,\n");
        sf.append("\tIFNULL(DATE_FORMAT(cbs.create_date, '%Y-%m-%d'), '') AS date,\n");
        sf.append("\tIFNULL(\n");
        sf.append("CASE cbs.speed_status\n");
        sf.append("WHEN '0' THEN 'HD待分派' \n");
        sf.append("WHEN '1' THEN '第三方已拒绝' \n");
        sf.append("WHEN '2' THEN '已分派第三方' \n");
        sf.append("WHEN '3' THEN '第三方已分配' \n");
        sf.append("WHEN '4' THEN '区域工程师已分配' \n");
        sf.append("WHEN '5' THEN '工程师已接受' \n");
        sf.append("WHEN '6' THEN '工程师实施中' \n");
        sf.append("WHEN '7' THEN '服务已完成' END ,'') as `status` \n");
        sf.append("FROM\n");
        sf.append("\tcbs_service cbs\n");
        sf.append("LEFT JOIN zy_cinema c ON c.id = cbs.ZY_CINEMA_ID\n");
        sf.append("LEFT JOIN t_s_territory tt ON tt.id = c.province \n");
        sf.append("LEFT JOIN cbs_service_type ty ON ty.id = cbs.service_type_2 \n");
        sf.append("LEFT JOIN cbs_service_type ty1 ON ty1.id = cbs.service_type_1 \n");
        StringBuffer sf1 = new StringBuffer();
        sf1.append(" GROUP BY cbs.id  ORDER BY cbs.create_date DESC ");


        StringBuffer conditionSql = new StringBuffer();  //条件
        List<Map<String, Object>> zyContacts = systemService.findForJdbc("SELECT id FROM zy_contacts WHERE base_id = '" + user.getId() + "'");
        //查找条件
        if (zyContacts.size() > 0) {
            if (type == 1) {
                /*待分配*/
//                conditionSql.append("WHERE cbs.speed_status = 2 AND list.assign_touser = '" + zyContacts.get(0).get("id") + "' and list.`status` !=1 and list.`status`!=2 and list.assign_torole='1'");
                conditionSql.append("WHERE cbs.speed_status = 2 AND cbs.zy_contact_id = '" + zyContacts.get(0).get("id") + "'");
            }
            if (type == 2) {
//                conditionSql.append("WHERE cbs.speed_status != 2  and cbs.speed_status != 1  and list.assign_touser = '" + zyContacts.get(0).get("id") + "' and list.`status` !=1 and list.`status`!=2 and list.assign_torole='1'");
                conditionSql.append("WHERE cbs.speed_status > 2  and cbs.zy_contact_id = '" + zyContacts.get(0).get("id") + "'");
                //已完成状态下，1个月内的单子
                conditionSql.append(" AND  (( DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <=cbs.close_time  and cbs.`status`='4' )or cbs.`status`='3' )");
            }
        }

        if (!styletype.equals("")) {
            if (styletype.equals("wx")) {
                conditionSql.append(" AND  ty1.`name` = '故障维修'");
            }
            if (styletype.equals("yj")) {  //移机
                conditionSql.append(" AND  ty.`name` = '移机'");
            }
            if (styletype.equals("yq")) {  //引擎翻新
                conditionSql.append(" AND  ty.`name` = '引擎翻新'");
            }
            if (styletype.equals("gg")) {  //光管翻新
                conditionSql.append(" AND  ty.`name` = '光管翻新'");
            }
            if (styletype.equals("xc")) {  //现场检测
                conditionSql.append(" AND  (ty.`name` = '现场检测' OR ty.`name` = '维保检测') ");
            }
        }
        if (!area.equals("")) {
            conditionSql.append(" AND tt.id ='").append(area).append("' ");
        }
        if (!esSn.equals("")) {
            conditionSql.append(" AND cbs.sn ='").append(esSn).append("' ");
        }
        if (!cmId.equals("")) {
            conditionSql.append(" AND c.id ='").append(cmId).append("' ");
        }

        String confitionSqlT = "";
        if (pageSize != 0 && counters != 0) { //分页查询,pagesize=0的情况，不分页
            int beginNum = 0;
            if (counters != 1) {
                beginNum = (counters - 1) * pageSize;
            }
            confitionSqlT = " LIMIT " + beginNum + "," + pageSize;
        }
        List<Map<String, Object>> detailMap_todo = super.findForJdbc(sf.toString() + conditionSql.toString() + sf1.toString() + confitionSqlT);
        rsMap.put("tpIpMapList_todo", detailMap_todo);  //查询所有


        if (StringUtil.isEmpty(TodoNum)) {
            //列表页上方 - 地区分组sql
            StringBuffer citySqlpart1 = new StringBuffer();
            citySqlpart1.append(" SELECT ");
            citySqlpart1.append(" 	tt.ID AS id, ");
            citySqlpart1.append(" 	tt.territoryname, ");
            citySqlpart1.append(" 	count(cbs.id) AS total ");
            citySqlpart1.append(" FROM ");
            citySqlpart1.append(" 	cbs_service cbs ");
            citySqlpart1.append(" LEFT JOIN zy_cinema c ON c.id = cbs.ZY_CINEMA_ID ");
            citySqlpart1.append(" INNER JOIN t_s_territory tt ON tt.id = c.province ");
            citySqlpart1.append(" LEFT JOIN cbs_service_type ty ON ty.id = cbs.service_type_2 ");
            citySqlpart1.append(" LEFT JOIN cbs_service_type ty1 ON ty1.id = cbs.service_type_1 ");
            StringBuffer citySqlpart2 = new StringBuffer();
            citySqlpart2.append(" GROUP BY ");
            citySqlpart2.append(" 	tt.ID ");
            citySqlpart2.append(" ORDER BY ");
            citySqlpart2.append(" 	tt.territorysort ");
            //根据地区分组
            List<Map<String, Object>> areaMapList = super.findForJdbc(citySqlpart1.toString() + conditionSql.toString() + citySqlpart2.toString());
            rsMap.put("areaMapList_todo", areaMapList); //按照省市区分
        }

        return rsMap;
    }

    @Override
    public Map<String, Object> queryAppIpTpAegList(String TodoNum, String styletype, int type, String area, String planYear, String planMonth, String esSn, String cmId) {
        Map<String, Object> rsMap = new HashMap<String, Object>();
        TSUser user = ResourceUtil.getSessionUserName();  //当前登录人

        //服务单内容
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT\n");
        sf.append("\tcbs.id AS id,\n");
        sf.append("\tCOUNT(cbs.id) AS total,\n");
        sf.append("\tIFNULL(cbs.`zy_cinema_name`, '') AS `name`,\n");
        sf.append("\tIFNULL(cbs.door_number,'0') AS doornumber,\n");
        sf.append("\tIFNULL(cbs.`level`, '') AS `level`,\n");
        sf.append("\tIFNULL(cbs.address, '') AS address,\n");
        sf.append("\tIFNULL(cbs.contacts_name, '') AS contactsname,\n");
        sf.append("\tIFNULL(cbs.description, '') AS description,\n");
        sf.append("\tIFNULL(cbs.contacts_phone, '') AS cellphone1,\n");
        sf.append("\tIFNULL('', '') AS cellphone2,\n");
        sf.append("\tIFNULL(cbs.type_dec, '') AS type,\n");
        sf.append("\tIFNULL(DATE_FORMAT(cbs.create_date, '%Y-%m-%d'), '') AS date,\n");
        sf.append("\tIFNULL(\n");
        sf.append("CASE cbs.speed_status\n");
        sf.append("WHEN '0' THEN 'HD待分派' \n");
        sf.append("WHEN '1' THEN '第三方已拒绝' \n");
        sf.append("WHEN '2' THEN '已分派第三方' \n");
        sf.append("WHEN '3' THEN '第三方已分配' \n");
        sf.append("WHEN '4' THEN '区域工程师已分配' \n");
        sf.append("WHEN '5' THEN '工程师已接受' \n");
        sf.append("WHEN '6' THEN '工程师实施中' \n");
        sf.append("WHEN '7' THEN '服务已完成' END ,'') as `status` \n");
        sf.append("FROM\n");
        sf.append("\tcbs_service cbs\n");
        sf.append("LEFT JOIN zy_cinema c ON c.id = cbs.ZY_CINEMA_ID\n");
        sf.append("LEFT JOIN t_s_territory tt ON tt.id = c.province \n");
        sf.append("LEFT JOIN cbs_service_type ty ON ty.id = cbs.service_type_2 \n");
        sf.append("LEFT JOIN cbs_service_type ty1 ON ty1.id = cbs.service_type_1 \n");
        StringBuffer sf1 = new StringBuffer();
        sf1.append(" GROUP BY cbs.id  ORDER BY cbs.create_date DESC ");


        StringBuffer conditionSql = new StringBuffer();  //条件
        List<ZyEngineerEntity> egs = systemService.findByProperty(ZyEngineerEntity.class, "tsBaseUser.id", user.getId());
        if (null != egs && egs.size() > 0) {
            if (type == 1) {
                /*待分配*/
//                conditionSql.append("WHERE cbs.speed_status = 3 AND list.assign_touser = '" + egs.get(0).getId() + "' and list.`status` !=1 and list.`status`!=2 and list.assign_torole='2'");
                conditionSql.append("WHERE cbs.speed_status = 3 and cbs.aeg_id = '" + egs.get(0).getId() + "'");
            }
            if (type == 2) {
                /*已分配*/
//                conditionSql.append("WHERE cbs.speed_status != 3 and cbs.speed_status != 2 and cbs.speed_status != 1  and list.assign_touser = '" + egs.get(0).getId() + "' and list.`status` !=1 and list.`status`!=2 and list.assign_torole='2'");
                conditionSql.append("WHERE cbs.speed_status > 3  and cbs.aeg_id = '" + egs.get(0).getId() + "'");
                //已完成状态下，1个月内的单子
                conditionSql.append(" AND  (( DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <=cbs.close_time  and cbs.`status`='4' )or cbs.`status`='3' )");

            }
        }

        if (!styletype.equals("")) {
            if (styletype.equals("wx")) {
                conditionSql.append(" AND  ty1.`name` = '故障维修'");
            }
            if (styletype.equals("yj")) {  //移机
                conditionSql.append(" and ty.`name` = '移机'");
            }
            if (styletype.equals("yq")) {  //引擎翻新
                conditionSql.append(" and ty.`name` = '引擎翻新'");
            }
            if (styletype.equals("gg")) {  //光管翻新
                conditionSql.append(" and ty.`name` = '光管翻新'");
            }
            if (styletype.equals("xc")) {  //现场检测
                conditionSql.append(" AND  (ty.`name` = '现场检测' OR ty.`name` = '维保检测')");
            }
        }

        if (!area.equals("")) {
            conditionSql.append(" AND tt.id ='").append(area).append("' ");
        }
        if (!esSn.equals("")) {
            conditionSql.append(" AND cbs.sn ='").append(esSn).append("' ");
        }
        if (!cmId.equals("")) {
            conditionSql.append(" AND c.id ='").append(cmId).append("' ");
        }

        List<Map<String, Object>> detailMap_todo = super.findForJdbc(sf.toString() + conditionSql.toString() + sf1.toString());
        rsMap.put("tpIpMapList_todo", detailMap_todo);  //查询所有


        if (StringUtil.isEmpty(TodoNum)) {
            //列表页上方 - 地区分组sql
            StringBuffer citySqlpart1 = new StringBuffer();
            citySqlpart1.append(" SELECT ");
            citySqlpart1.append(" 	tt.ID AS id, ");
            citySqlpart1.append(" 	tt.territoryname, ");
            citySqlpart1.append(" 	count(cbs.id) AS total ");
            citySqlpart1.append(" FROM ");
            citySqlpart1.append(" 	cbs_service cbs ");
            citySqlpart1.append(" LEFT JOIN zy_cinema c ON c.id = cbs.ZY_CINEMA_ID ");
            citySqlpart1.append(" INNER JOIN t_s_territory tt ON tt.id = c.province ");
            citySqlpart1.append(" LEFT JOIN cbs_service_type ty ON ty.id = cbs.service_type_2 ");
            citySqlpart1.append(" LEFT JOIN cbs_service_type ty1 ON ty1.id = cbs.service_type_1 ");
            StringBuffer citySqlpart2 = new StringBuffer();
            citySqlpart2.append(" GROUP BY ");
            citySqlpart2.append(" 	tt.ID ");
            citySqlpart2.append(" ORDER BY ");
            citySqlpart2.append(" 	tt.territorysort ");
            //根据地区分组
            List<Map<String, Object>> areaMapList = super.findForJdbc(citySqlpart1.toString() + conditionSql.toString() + citySqlpart2.toString());
            rsMap.put("areaMapList_todo", areaMapList); //按照省市区分
        }
        return rsMap;
    }

    @Override
    public Map<String, Object> queryAppIpTpEgList(String TodoNum, String styletype, int type, String area, String planYear, String planMonth, String esSn, String cmId) {
        Map<String, Object> rsMap = new HashMap<String, Object>();
        TSUser user = ResourceUtil.getSessionUserName();  //当前登录人
        List<ZyEngineerEntity> zyEngineerEntities = systemService.findByProperty(ZyEngineerEntity.class, "tsBaseUser.id", user.getId());

        //服务单内容
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT\n");
        sf.append("\tcbs.id AS id,\n");
        sf.append("\tCOUNT(cbs.id) AS total,\n");
        sf.append("\tIFNULL(cbs.`door_number`, '0') AS `doornumber`,\n");
        sf.append("\tIFNULL(cbs.`zy_cinema_name`, '') AS `name`,\n");
        sf.append("\tIFNULL(cbs.`level`, '') AS `level`,\n");
        sf.append("\tIFNULL(cbs.address, '') AS address,\n");
        sf.append("\tIFNULL(cbs.contacts_name, '') AS contactsname,\n");
        sf.append("\tIFNULL(cbs.description, '') AS description,\n");
        sf.append("\tIFNULL(cbs.contacts_phone, '') AS cellphone1,\n");
        sf.append("\tIFNULL('', '') AS cellphone2,\n");
        sf.append("\tIFNULL(cbs.type_dec, '') AS type,\n");
        sf.append("\tIFNULL(DATE_FORMAT(cbs.create_date, '%Y-%m-%d'), '') AS date,\n");
        sf.append("\tIFNULL(\n");
        sf.append("CASE cbs.speed_status\n");
        sf.append("WHEN '0' THEN 'HD待分派' \n");
        sf.append("WHEN '1' THEN '第三方已拒绝' \n");
        sf.append("WHEN '2' THEN '已分派第三方' \n");
        sf.append("WHEN '3' THEN '第三方已分配' \n");
        sf.append("WHEN '4' THEN '区域工程师已分配' \n");
        sf.append("WHEN '5' THEN '工程师已接受' \n");
        sf.append("WHEN '6' THEN '工程师实施中' \n");
        sf.append("WHEN '7' THEN '服务已完成' END ,'') as STATUS \n");
        sf.append("FROM\n");
        sf.append("\tcbs_service cbs\n");
//        sf.append("LEFT JOIN cbs_service_assign_list list ON list.service_id = cbs.id\n");
        sf.append("LEFT JOIN zy_cinema c ON c.id = cbs.ZY_CINEMA_ID\n");
        sf.append("LEFT JOIN t_s_territory tt ON tt.id = c.province \n");
        sf.append("LEFT JOIN cbs_service_type ty ON ty.id = cbs.service_type_2  \n");
        sf.append("LEFT JOIN cbs_service_type ty1 ON ty1.id = cbs.service_type_1  \n");
        StringBuffer sf1 = new StringBuffer();
        sf1.append(" GROUP BY cbs.id  ORDER BY cbs.create_date DESC ");


        StringBuffer conditionSql = new StringBuffer();  //条件
        if (zyEngineerEntities.size() > 0) {
            //查找条件
            if (type == 1) {
                /*待分配 19年1月18调优*/
//                conditionSql.append("  WHERE (cbs.speed_status = 3 or cbs.speed_status = 4) AND list.assign_touser = '" + zyEngineerEntities.get(0).getId() + "' and list.`status` !=1 and list.`status`!=2 and list.assign_torole='3' ");
                conditionSql.append("  WHERE (cbs.speed_status = 3 or cbs.speed_status = 4) and cbs.engineer_id = '" + zyEngineerEntities.get(0).getId() + "' ");
            }
            if (type == 2) {
//                /*处理中*/
//                conditionSql.append("  WHERE (cbs.speed_status = 5 or cbs.speed_status = 6) and list.assign_touser = '" + zyEngineerEntities.get(0).getId() + "' and list.`status` !=1 and list.`status`!=2 and list.assign_torole='3' ");
                conditionSql.append("  WHERE (cbs.speed_status = 5 or cbs.speed_status = 6) and cbs.engineer_id = '" + zyEngineerEntities.get(0).getId() + "' ");
            }
            if (type == 3) {
//                /*待返件*/
//                conditionSql.append("INNER JOIN cbs_return_equipment ceq ON ceq.cr_code = cbs.`code`  AND ceq.`status` = 1  WHERE cbs.status = 4 and list.assign_touser = '" + zyEngineerEntities.get(0).getId() + "' and list.`status` !=1 and list.`status`!=2 and list.assign_torole='3'");
                conditionSql.append("INNER JOIN cbs_return_equipment ceq ON ceq.cr_code = cbs.`code`  AND ceq.`status` = 1  WHERE cbs.status = 4 and cbs.engineer_id = '" + zyEngineerEntities.get(0).getId() + "' ");
            }
            if (type == 4) {
//                /*已完成*/
                conditionSql.append("LEFT JOIN cbs_return_equipment ceq ON ceq.cr_code = cbs.`code` AND ceq.`status` != 1 WHERE cbs.status = 4  and cbs.engineer_id = '" + zyEngineerEntities.get(0).getId() + "' ");
//                //已完成状态下，1个月内的单子
                conditionSql.append(" AND DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <=cbs.close_time ");

            }
        }

        if (!styletype.equals("")) {
            if (styletype.equals("wx")) {
                conditionSql.append(" AND  ty1.`name` = '故障维修'");
            }
            if (styletype.equals("yj")) {  //移机
                conditionSql.append(" and ty.`name` = '移机'");
            }
            if (styletype.equals("yq")) {  //引擎翻新
                conditionSql.append(" and ty.`name` = '引擎翻新'");
            }
            if (styletype.equals("gg")) {  //光管翻新
                conditionSql.append(" and ty.`name` = '光管翻新'");
            }
            if (styletype.equals("xc")) {  //现场检测
                conditionSql.append(" and (ty.`name` = '现场检测' OR ty.`name` = '维保检测')");
            }
        }
        if (!area.equals("")) {
            conditionSql.append(" AND tt.id ='").append(area).append("' ");
        }
        if (!esSn.equals("")) {
            conditionSql.append(" AND cbs.sn ='").append(esSn).append("' ");
        }
        if (!cmId.equals("")) {
            conditionSql.append(" AND c.id ='").append(cmId).append("' ");
        }

        List<Map<String, Object>> detailMap_todo = super.findForJdbc(sf.toString() + conditionSql.toString() + sf1.toString());
        rsMap.put("tpIpMapList_todo", detailMap_todo);  //查询所有


        if (StringUtil.isEmpty(TodoNum)) {
            //列表页上方 - 地区分组sql
            StringBuffer citySqlpart1 = new StringBuffer();
            citySqlpart1.append(" SELECT ");
            citySqlpart1.append(" 	tt.ID AS id, ");
            citySqlpart1.append(" 	tt.territoryname, ");
            citySqlpart1.append(" 	count(cbs.id) AS total ");
            citySqlpart1.append(" FROM ");
            citySqlpart1.append(" 	cbs_service cbs ");
//            citySqlpart1.append(" LEFT JOIN cbs_service_assign_list list ON list.service_id = cbs.id\n");
            citySqlpart1.append(" LEFT JOIN zy_cinema c ON c.id = cbs.ZY_CINEMA_ID ");
            citySqlpart1.append(" INNER JOIN t_s_territory tt ON tt.id = c.province ");
            citySqlpart1.append(" LEFT JOIN cbs_service_type ty ON ty.id = cbs.service_type_2  ");
            citySqlpart1.append(" LEFT JOIN cbs_service_type ty1 ON ty1.id = cbs.service_type_1  ");
            StringBuffer citySqlpart2 = new StringBuffer();
            citySqlpart2.append(" GROUP BY ");
            citySqlpart2.append(" 	tt.ID ");
            citySqlpart2.append(" ORDER BY ");
            citySqlpart2.append(" 	tt.territorysort ");

            //根据地区分组
//            System.out.println("按照省份查找的SQL===" + (citySqlpart1.toString() + conditionSql.toString() + citySqlpart2.toString()));
            List<Map<String, Object>> areaMapList = super.findForJdbc(citySqlpart1.toString() + conditionSql.toString() + citySqlpart2.toString());
            rsMap.put("areaMapList_todo", areaMapList); //按照省市区分
        }
        return rsMap;
    }

    @Override
    public void queryDatagridResultForChooseContacters(HttpServletRequest request, DataGrid dataGrid, String roleCode) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" ct.id, ");
        sql.append(" bu.realname AS name,IFNULL(ct.cellphone1,'') AS cellphone1, IFNULL(ct.cellphone2,'') AS cellphone2, IFNULL(ct.email,'') AS email");
        sql.append(" FROM ");
        sql.append(" t_s_base_user bu ");
        sql.append(" LEFT JOIN zy_contacts ct on ct.base_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_user_org uo on uo.user_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_depart dp on dp.ID = uo.org_id ");
        sql.append(" LEFT JOIN t_s_role_user ru on ru.userid = bu.ID ");
        sql.append(" LEFT JOIN t_s_role ro on ro.ID = ru.roleid ");
        sql.append(" WHERE ");
        sql.append(" dp.org_code = '").append(roleCode == null ? "" : roleCode).append("' ");
        sql.append(" AND dp.logic_delete = 0 ");
        sql.append(" AND ro.rolecode = '").append(StaticValue.RULE_CODE_THIRDPARTY_HEADER).append("'");
        Map<String, String[]> paramsMap = request.getParameterMap();
        int totalCount = 0;
        Integer page = 1, rows = 10;
        rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
        page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");

        if (StringUtil.isNotEmpty(paramsMap.get("name")) && StringUtil.isNotEmpty(paramsMap.get("name")[0])) {
            sql.append(" AND bu.name LIKE '%").append(paramsMap.get("name")[0].toString()).append("%' ");
        }
        if (StringUtil.isNotEmpty(paramsMap.get("cellphone1")) && StringUtil.isNotEmpty(paramsMap.get("cellphone1")[0])) {
            sql.append(" AND ct.cellphone1 LIKE '%").append(paramsMap.get("cellphone1")[0].toString()).append("%' ");
        }
        if (StringUtil.isNotEmpty(paramsMap.get("cellphone2")) && StringUtil.isNotEmpty(paramsMap.get("cellphone2")[0])) {
            sql.append(" AND ct.cellphone2 LIKE '%").append(paramsMap.get("cellphone2")[0].toString()).append("%' ");
        }
        if (StringUtil.isNotEmpty(paramsMap.get("email")) && StringUtil.isNotEmpty(paramsMap.get("email")[0])) {
            sql.append(" AND ct.email LIKE '%").append(paramsMap.get("email")[0].toString()).append("%' ");
        }
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        if (rows == 0 && page == 0) {
            rsMapList = findForJdbc(sql.toString());
        } else {
            dataGrid.setPage(page);
            dataGrid.setRows(rows);
            rsMapList = findForJdbc(sql.toString(), page, rows);
        }
        List<Map<String, Object>> countMapList = super.findForJdbc(sql.toString());
        if (null != countMapList && countMapList.size() > 0) {
            totalCount = countMapList.size();
        }
        dataGrid.setTotal(totalCount);
        dataGrid.setResults(rsMapList);
    }

    @Override
    public void queryDatagridResultForChooseEngineers(HttpServletRequest request, DataGrid dataGrid, String orgCode, String planIds) {
        Criteria c = getSession().createCriteria(ZyInspectionPlanEntity.class);
        c.add(Restrictions.in("id", planIds.split(",")));
        c.add(Restrictions.eq("lightPath", StaticValue.DIC_LIGHT_PATH_02));
        queryForAreaEngineerByOrgCodeAndRoleCode(request, dataGrid, orgCode, StaticValue.RULE_CODE_AREA_ENGINEER, StaticValue.DIC_ENGINEER_STATE_ONJOB);
    }

    @Override
    public void queryDatagridResultForChooseEgEnter(HttpServletRequest request, DataGrid dataGrid, String orgCode, String planIds) {
        Criteria c = getSession().createCriteria(ZyInspectionPlanEntity.class);
        c.add(Restrictions.in("id", planIds.split(",")));
        c.add(Restrictions.eq("lightPath", StaticValue.DIC_LIGHT_PATH_02));
        queryForEngineerByOrgCodeAndRoleCode(request, dataGrid, orgCode, StaticValue.RULE_CODE_THIRDPARTY_ENGINEER, StaticValue.DIC_ENGINEER_STATE_ONJOB);
    }

    @Override
    public List<Map<String, Object>> getCbsAPPServiceByCid(String cid) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tcbs.id AS 'id',\n" +
                "\tIFNULL(cbs.sn, cbs.sn_server) AS sn,\n" +
                "\tIFNULL(cbs.`code`, '') AS 'code',\n" +
                "\tIFNULL(cbs.`so`, '') AS 'so',\n" +
                "\tIFNULL(cbs.`level`, '') AS `level`,\n" +
                "\tIFNULL(cbs.`error_code`, '') AS 'errorCode',\n" +
                "\tIFNULL(cbs.`description`, '') AS 'description',\n" +
                "\tIFNULL(cbs.`status`, '') AS 'status',\n" +
                "\tIFNULL(cbs.`handle_des`, '') AS 'judge400',\n" +
                "\tIFNULL(cbs.`assign_des`, '') AS 'assigndes',\n" +
                "\tIFNULL(cbs.`authentication`, '') AS 'authentication',\n" +
                "\tIFNULL(DATE_FORMAT(cbs.assign_time, '%Y-%m-%d'), '') AS assignTime,\n" +
                "\tCASE cbs.speed_status\n" +
                "WHEN '" + StaticValue.dfp + "' THEN\n" +
                "\t'HD待分配'\n" +
                "WHEN '" + StaticValue.dsfyjj + "' THEN\n" +
                "\t'第三方已拒绝'\n" +
                "WHEN '" + StaticValue.tfpdsf + "' THEN\n" +
                "\t'已分派第三方'\n" +
                "WHEN '" + StaticValue.dsfyfp + "' THEN\n" +
                "\t'第三方已分配'\n" +
                "WHEN '" + StaticValue.qygcsyfp + "' THEN\n" +
                "\t'区域工程师已分配'\n" +
                "WHEN '" + StaticValue.gcsyjs + "' THEN\n" +
                "\t'工程师已接受'\n" +
                "WHEN '" + StaticValue.gcsssz + "' THEN\n" +
                "\t'工程师实施中'\n" +
                "WHEN '" + StaticValue.fwywc + "' THEN\n" +
                "\t'服务已完成'\n" +
                "END AS speed,\n" +
                " IFNULL(cbs.`zy_cinema_name`, '') AS 'cinemaName',\n" +
                " IFNULL(cbs.address, '') AS address,\n" +
                " IFNULL(cbs.type_intheater_name, '') AS 'intheaterName',\n" +
                " IFNULL(cbs.contacts_name, '') AS contactsName,\n" +
                " IFNULL(cbs.contacts_phone, '') AS phone,\n" +
                " IFNULL(d.shortname, '') AS serviceProvider,\n" +
                "CASE sp.laser_reconstruction\n" +
                "WHEN '1' THEN\n" +
                "\t'是'\n" +
                "ELSE\n" +
                "\t'否'\n" +
                "END AS laserreconstruction,\n" +
                "CASE\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\tsp.repair_end_date\n" +
                ") < 0 THEN\n" +
                "\t'保外'\n" +
                "WHEN TIMESTAMPDIFF(\n" +
                "\tDAY,\n" +
                "\tNOW(),\n" +
                "\tsp.repair_end_date\n" +
                ") > 0 THEN\n" +
                "\t'保内'\n" +
                "ELSE\n" +
                "\t''\n" +
                "END  AS inReparirDateValService,\n" +
                "IFNULL(t.type_desc, '') AS typedesc\n" +
                "FROM\n" +
                "\t`cbs_service` cbs\n" +
                "LEFT JOIN `zy_cinema` zy ON cbs.zy_cinema_id = zy.id\n" +
                "LEFT JOIN zy_contacts co ON cbs.ZY_CONTACTS_ID = co.id\n" +
                "LEFT JOIN zy_equipment_spare sp ON sp.id = cbs.ZY_EQUIPMENT_SPARE_ID\n" +
                "LEFT JOIN zy_equipment_type t ON t.id = sp.type\n" +
                "LEFT JOIN t_s_depart AS d ON d.bp_code = sp.bp_no ");
        sb.append(" WHERE ");
        sb.append("cbs.id = ? LIMIT 1");
        return systemService.findForJdbc(sb.toString(), cid);
    }

    @Override
    public List<Map<String, Object>> queryEngineerForAppEngName(String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * from ( \n");
        sql.append(" SELECT\n");
        sql.append("\teg.id,\n");
        sql.append("\teg.`name`,\n");
        sql.append("\teg.cellphone,\n");
        sql.append("\teg.email,\n");
        sql.append("\teg.state,\n");
        sql.append("\teg.authentication,\n");
        sql.append("\tbu.realname AS realname,\n");
        sql.append("\tMIN(ro.rolecode) AS rolecode,\n");
        sql.append("\tro.id AS roid,\n");
        sql.append("\tdp.id AS dpid,\n");
        sql.append("\tdp.org_code AS CODE,\n");
        sql.append("\tcount(eg1.id) AS size\n");
        sql.append("FROM\n");
        sql.append("\tzy_engineer eg\n");
        sql.append("LEFT JOIN zy_engineer eg1 ON eg1.parent_id = eg.id\n");
        sql.append("LEFT JOIN t_s_base_user bu ON eg.ts_base_user_id = bu.ID\n");
        sql.append("LEFT JOIN t_s_user u ON u.id = bu.ID\n");
        sql.append("LEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID\n");
        sql.append("LEFT JOIN t_s_depart dp ON dp.ID = uo.org_id\n");
        sql.append("LEFT JOIN t_s_role_user ru ON ru.userid = bu.ID\n");
        sql.append("LEFT JOIN t_s_role ro ON ro.ID = ru.roleid\n");
        sql.append("WHERE eg.id=?  ");
        sql.append(" AND eg.is_delete = 0  AND eg.id IS NOT NULL GROUP BY eg.id ");
        sql.append(" UNION ");
        sql.append("SELECT\n");
        sql.append("\tc.id,\n");
        sql.append("\tc.`name`,\n");
        sql.append("\tc.cellphone1,\n");
        sql.append("\tc.email,\n");
        sql.append("\tc.state,\n");
        sql.append("\tc.authentication,\n");
        sql.append("\tbu.realname AS realname,\n");
        sql.append("\tMIN(ro.rolecode) AS rolecode,\n");
        sql.append("\tro.id AS roid,\n");
        sql.append("\tdp.id AS dpid,\n");
        sql.append("\tdp.org_code AS CODE,\n");
        sql.append("\t0 AS size\n");
        sql.append("FROM\n");
        sql.append("\tzy_contacts c\n");
        sql.append("LEFT JOIN t_s_base_user bu ON bu.id = c.base_id\n");
        sql.append("LEFT JOIN t_s_user u ON u.id = bu.ID\n");
        sql.append("LEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID\n");
        sql.append("LEFT JOIN t_s_depart dp ON dp.ID = uo.org_id\n");
        sql.append("LEFT JOIN t_s_role_user ru ON ru.userid = bu.ID\n");
        sql.append("LEFT JOIN t_s_role ro ON ro.ID = ru.roleid\n");
        sql.append("WHERE\n");
        sql.append("\tc.id = ?\n");
        sql.append("AND c.id IS NOT NULL");
        sql.append(" ) as a LIMIT 1 ");

        return super.findForJdbc(sql.toString(), id, id);
    }

    //获取工程师未完成的数量
    @Override
    public List<Map<String, Object>> queryEngineerWsizeForAppEngName(String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n");
        sql.append("\tifnull(count(s.id),0) as wsize\n");
        sql.append("FROM\n");
        sql.append("\tzy_engineer eg\n");
        sql.append("LEFT JOIN cbs_service_assign_list l ON l.assign_touser = eg.id\n");
        sql.append("AND l.assign_type = 4 and l.status != 1 and l.status != 2 \n");
        sql.append("LEFT JOIN cbs_service s ON s.id = l.service_id\n");
        sql.append("AND s.`status` != - 1\n");
        sql.append("AND s.`status` != 4\n");
        sql.append("AND s.`status` != 5\n");
        sql.append(" where eg.id=?  ");
        sql.append("AND eg.is_delete = 0 ");
        return super.findForJdbc(sql.toString(), id);
    }

    @SuppressWarnings("unchecked")
    @Override
    public Map queryAppAegOrEgTodoIps(String TodoNum) {
        TSUser user = ResourceUtil.getSessionUserName();
        List<TSRoleUser> tsRoleUser = systemService.findHql("from TSRoleUser where TSUser.id = ?", user.getId());
        int userType = 1;
        if (null != tsRoleUser && tsRoleUser.size() > 0) {
            for (TSRoleUser rsRU : tsRoleUser) {
                //优先级 第三方 > 区域工程师 > 工程师
                if (rsRU.getTSRole().getRoleCode().equals(StaticValue.RULE_CODE_THIRDPARTY_HEADER)) {
                    userType = 1;
                    break;
                }
                if (rsRU.getTSRole().getRoleCode().equals(StaticValue.RULE_CODE_AREA_ENGINEER)) {
                    userType = 2;
                    break;
                }
                if (rsRU.getTSRole().getRoleCode().equals(StaticValue.RULE_CODE_THIRDPARTY_ENGINEER)) {
                    userType = 3;
                    break;
                }
            }
        }
        Map<String, Object> todo = new HashMap<String, Object>();
        int todoTaskCountWX = 0;
        int todoTaskCountYJ = 0;
        int todoTaskCountYQ = 0;
        int todoTaskCountGG = 0;
        int todoTaskCountXC = 0;
        Map<String, Object> rsMapWX = new HashMap<String, Object>();  /*普通维修*/
        Map<String, Object> rsMapYJ = new HashMap<String, Object>();   /*移机*/
        Map<String, Object> rsMapYQ = new HashMap<String, Object>();   /*引擎翻新*/
        Map<String, Object> rsMapGG = new HashMap<String, Object>();   /*光管翻新*/
        Map<String, Object> rsMapXC = new HashMap<String, Object>();   /*现场检测*/
        if (userType == 1) {
            rsMapWX = queryAppIpTpList(TodoNum, "wx", 1, "", "", "", "", "", 0, 0);
            if (null != rsMapWX.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapWX.get("tpIpMapList_todo")).size() > 0) {
                for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapWX.get("tpIpMapList_todo")) {
                    if (null != mapList && null != mapList.get("total")) {
                        todoTaskCountWX += Integer.parseInt(mapList.get("total").toString());
                    }
                }
            }

            rsMapYJ = queryAppIpTpList(TodoNum, "yj", 1, "", "", "", "", "", 0, 0);
            if (null != rsMapYJ.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapYJ.get("tpIpMapList_todo")).size() > 0) {
                for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapYJ.get("tpIpMapList_todo")) {
                    if (null != mapList && null != mapList.get("total")) {
                        todoTaskCountYJ += Integer.parseInt(mapList.get("total").toString());
                    }
                }
            }

            rsMapYQ = queryAppIpTpList(TodoNum, "yq", 1, "", "", "", "", "", 0, 0);
            if (null != rsMapYQ.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapYQ.get("tpIpMapList_todo")).size() > 0) {
                for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapYQ.get("tpIpMapList_todo")) {
                    if (null != mapList && null != mapList.get("total")) {
                        todoTaskCountYQ += Integer.parseInt(mapList.get("total").toString());
                    }
                }
            }

            rsMapGG = queryAppIpTpList(TodoNum, "gg", 1, "", "", "", "", "", 0, 0);
            if (null != rsMapGG.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapGG.get("tpIpMapList_todo")).size() > 0) {
                for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapGG.get("tpIpMapList_todo")) {
                    if (null != mapList && null != mapList.get("total")) {
                        todoTaskCountGG += Integer.parseInt(mapList.get("total").toString());
                    }
                }
            }

            rsMapXC = queryAppIpTpList(TodoNum, "xc", 1, "", "", "", "", "", 0, 0);
            if (null != rsMapXC.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapXC.get("tpIpMapList_todo")).size() > 0) {
                for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapXC.get("tpIpMapList_todo")) {
                    if (null != mapList && null != mapList.get("total")) {
                        todoTaskCountXC += Integer.parseInt(mapList.get("total").toString());
                    }
                }
            }


        } else if (userType == 2 || userType == 3) {//区域工程师 or 工程师
            if (userType != 3) { //工程师不累加这一项
                rsMapWX = queryAppIpTpAegList(TodoNum, "wx", 1, "", "", "", "", "");
                if (null != rsMapWX.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapWX.get("tpIpMapList_todo")).size() > 0) {
                    for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapWX.get("tpIpMapList_todo")) {
                        if (null != mapList && null != mapList.get("total")) {
                            todoTaskCountWX += Integer.parseInt(mapList.get("total").toString());
                        }
                    }
                }

                rsMapYJ = queryAppIpTpAegList(TodoNum, "yj", 1, "", "", "", "", "");
                if (null != rsMapYJ.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapYJ.get("tpIpMapList_todo")).size() > 0) {
                    for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapYJ.get("tpIpMapList_todo")) {
                        if (null != mapList && null != mapList.get("total")) {
                            todoTaskCountYJ += Integer.parseInt(mapList.get("total").toString());
                        }
                    }
                }

                rsMapYQ = queryAppIpTpAegList(TodoNum, "yq", 1, "", "", "", "", "");
                if (null != rsMapYQ.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapYQ.get("tpIpMapList_todo")).size() > 0) {
                    for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapYQ.get("tpIpMapList_todo")) {
                        if (null != mapList && null != mapList.get("total")) {
                            todoTaskCountYQ += Integer.parseInt(mapList.get("total").toString());
                        }
                    }
                }


                rsMapGG = queryAppIpTpAegList(TodoNum, "gg", 1, "", "", "", "", "");
                if (null != rsMapGG.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapGG.get("tpIpMapList_todo")).size() > 0) {
                    for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapGG.get("tpIpMapList_todo")) {
                        if (null != mapList && null != mapList.get("total")) {
                            todoTaskCountGG += Integer.parseInt(mapList.get("total").toString());
                        }
                    }
                }

                rsMapXC = queryAppIpTpAegList(TodoNum, "xc", 1, "", "", "", "", "");
                if (null != rsMapXC.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapXC.get("tpIpMapList_todo")).size() > 0) {
                    for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapXC.get("tpIpMapList_todo")) {
                        if (null != mapList && null != mapList.get("total")) {
                            todoTaskCountXC += Integer.parseInt(mapList.get("total").toString());
                        }
                    }
                }

            }
            rsMapWX = queryAppIpTpEgList(TodoNum, "wx", 1, "", "", "", "", "");
            if (null != rsMapWX.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapWX.get("tpIpMapList_todo")).size() > 0) {
                for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapWX.get("tpIpMapList_todo")) {
                    if (null != mapList && null != mapList.get("total")) {
                        todoTaskCountWX += Integer.parseInt(mapList.get("total").toString());
                    }
                }
            }


            rsMapYJ = queryAppIpTpEgList(TodoNum, "yj", 1, "", "", "", "", "");
            if (null != rsMapYJ.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapYJ.get("tpIpMapList_todo")).size() > 0) {
                for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapYJ.get("tpIpMapList_todo")) {
                    if (null != mapList && null != mapList.get("total")) {
                        todoTaskCountYJ += Integer.parseInt(mapList.get("total").toString());
                    }
                }
            }

            rsMapYQ = queryAppIpTpEgList(TodoNum, "yq", 1, "", "", "", "", "");
            if (null != rsMapYQ.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapYQ.get("tpIpMapList_todo")).size() > 0) {
                for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapYQ.get("tpIpMapList_todo")) {
                    if (null != mapList && null != mapList.get("total")) {
                        todoTaskCountYQ += Integer.parseInt(mapList.get("total").toString());
                    }
                }
            }

            rsMapGG = queryAppIpTpEgList(TodoNum, "gg", 1, "", "", "", "", "");
            if (null != rsMapGG.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapGG.get("tpIpMapList_todo")).size() > 0) {
                for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapGG.get("tpIpMapList_todo")) {
                    if (null != mapList && null != mapList.get("total")) {
                        todoTaskCountGG += Integer.parseInt(mapList.get("total").toString());
                    }
                }
            }

            rsMapXC = queryAppIpTpEgList(TodoNum, "xc", 1, "", "", "", "", "");
            if (null != rsMapXC.get("tpIpMapList_todo") && ((List<Map<String, Object>>) rsMapXC.get("tpIpMapList_todo")).size() > 0) {
                for (Map<String, Object> mapList : (List<Map<String, Object>>) rsMapXC.get("tpIpMapList_todo")) {
                    if (null != mapList && null != mapList.get("total")) {
                        todoTaskCountXC += Integer.parseInt(mapList.get("total").toString());
                    }
                }
            }

        }

        todo.put("todoTaskCountWX", todoTaskCountWX);
        todo.put("todoTaskCountYQ", todoTaskCountYQ);
        todo.put("todoTaskCountGG", todoTaskCountGG);
        todo.put("todoTaskCountXC", todoTaskCountXC);
        todo.put("todoTaskCountYJ", todoTaskCountYJ);

        return todo;
    }

    @Override
    public List<Map<String, Object>> dontService(String sn) {
        String sql = "SELECT\n" +
                "\tCASE eq2.type_desc\n" +
                "WHEN '服务器' THEN\n" +
                "\t'Y'\n" +
                "ELSE\n" +
                "\t'N'\n" +
                "END AS type\n" +
                "FROM\n" +
                "\tzy_equipment_spare s\n" +
                "LEFT JOIN zy_equipment_type eq ON s.type = eq.id\n" +
                "LEFT JOIN zy_equipment_type eq1 ON eq.parent_id = eq1.id\n" +
                "LEFT JOIN zy_equipment_type eq2 ON eq1.parent_id = eq2.id\n" +
                "WHERE\n" +
                "\ts.sn = '" + sn + "'";
        List<Map<String, Object>> results = systemService.findForJdbc(sql);
        return results;
    }

    @Override
    public List<Map<String, Object>> getSLABySn(String sn) {
        String sql = "SELECT\n" +
                "IFNULL(c.serviceName,'') as serviceName,\n" +
                "IFNULL(c.description,'') as description\n" +
                "FROM\n" +
                "\tcbs_contract c LEFT JOIN cbs_contract_mc_equipment cme ON c.contract_code = cme.contract_code\n" +
                "WHERE\n" +
                "\tcme.sn = '" + sn + "'";
        List<Map<String, Object>> results = systemService.findForJdbc(sql);
        return results;
    }

    @Override
    public List<Map<String, Object>> getZyCinemaByName(String name) {
        //根据影院名称。查找影院信息
        List<Map<String, Object>> zyCinemaEntityList = systemService.findForJdbc("SELECT id from zy_cinema where `name` LIKE '%" + name + "%'");
        return zyCinemaEntityList;
    }

    @Override
    public List<Map<String, Object>> getZyEquipmentBySn(String sn) {
        //根据影院名称。查找影院信息
        List<Map<String, Object>> zyEquipmentList = systemService.findForJdbc("SELECT id from zy_equipment_spare where `sn`='" + sn + "'");
        return zyEquipmentList;
    }

    /*每天20点执行：完工单的完工日期后超过48小时未提交返件*/
    @Override
    public void unCommittedReturn() {
        //现场检测完工报告和远程完工报告中的服务单id
        List<String> id_list1 = systemService.findListbySql("select service_id from CBS_SCENE_REPORT where to_days(now())-to_days(finish_time)>2");
        //现场检测完工单中的服务单id
        List<String> id_list2 = systemService.findListbySql("select service_id from CBS_SCENE_CHECK_REPORT where to_days(now())-to_days(finish_time)>2");
        //引擎翻新现场检测完工单中的服务单id
        List<String> id_list3 = systemService.findListbySql("select service_id from CBS_ENGINE_SCENE_CHECK_REPORT where to_days(now())-to_days(finish_time)>2");
        //引擎翻新验收单中的服务单id
        List<String> id_list4 = systemService.findListbySql("select service_id from CBS_ENGINE_ACCEPT_REPORT where to_days(now())-to_days(finish_time)>2");
        //光管翻新完工单中的服务单id
        List<String> id_list5 = systemService.findListbySql("select service_id from CBS_LIGHT_REPORT where to_days(now())-to_days(finish_time)>2");
        //技术支持完工单中的服务单id
        List<String> id_list6 = systemService.findListbySql("select service_id from cbs_techology_agree_report where to_days(now())-to_days(finish_time)>2");
        List<String> list = new ArrayList<>();
        if (id_list1.size() != 0) {
            for (int i = 0; i < id_list1.size(); i++) {
                List<String> codeList = systemService.findListbySql("select code from cbs_service where id = '" + id_list1.get(i) + "'");
                list.add(codeList.get(0));
            }
        }
        if (id_list2.size() != 0) {
            for (int i = 0; i < id_list2.size(); i++) {
                List<String> codeList = systemService.findListbySql("select code from cbs_service where id = '" + id_list2.get(i) + "'");
                list.add(codeList.get(0));
            }
        }
        if (id_list3.size() != 0) {
            for (int i = 0; i < id_list3.size(); i++) {
                List<String> codeList = systemService.findListbySql("select code from cbs_service where id = '" + id_list3.get(i) + "'");
                list.add(codeList.get(0));
            }
        }
        if (id_list4.size() != 0) {
            for (int i = 0; i < id_list4.size(); i++) {
                List<String> codeList = systemService.findListbySql("select code from cbs_service where id = '" + id_list4.get(i) + "'");
                list.add(codeList.get(0));
            }
        }
        if (id_list5.size() != 0) {
            for (int i = 0; i < id_list5.size(); i++) {
                List<String> codeList = systemService.findListbySql("select code from cbs_service where id = '" + id_list5.get(i) + "'");
                list.add(codeList.get(0));
            }
        }
        if (id_list6.size() != 0) {
            for (int i = 0; i < id_list6.size(); i++) {
                List<String> codeList = systemService.findListbySql("select code from cbs_service where id = '" + id_list6.get(i) + "'");
                list.add(codeList.get(0));
            }
        }
        if (list.size() != 0) {
            for (int i = 0; i < list.size(); i++) {
                List<CbsReturnEquipmentEntity> returnEquipmentEntityList = systemService.findHql("from CbsReturnEquipmentEntity where crCode = '" + list.get(i) + "'");
                if (returnEquipmentEntityList.size() != 0) {
                    for (int j = 0; j < returnEquipmentEntityList.size(); j++) {
                        if (StaticValue.RETURN_STATUS_Log_Type_1.equals(returnEquipmentEntityList.get(j).getStatus())) {
                            List<String> cellphoneList = systemService.findListbySql("select cellphone from zy_engineer where id in (select engineer_id from cbs_service where code = '" + list.get(i) + "')");
                            if (cellphoneList.size() != 0) {
                                String content = "服务单号<" + list.get(i) + ">尚未提交返件，请及时提交。具体内容请查看CBS系统";
                                SendMessage.sendSMSWithContent(cellphoneList.get(0), content, systemService);
                            }
                            break;
                        }
                    }
                }
            }
        }
    }

    @Override
    public ZyFileLogEntity getAppServiceQrCode(String cbsId) {
        //文件夹是否存在，不存在创建
        FileUtil.isMkdir(StaticValue.QrCode_UPLOAD_PATH);
        File file = null;
        boolean flag = true;
        ZyFileLogEntity zyfileLog = null;
        long Srdate = new Date().getTime();  //当前时间，时间戳格式
        SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmm"); //时间格式年月日时分 1805311056
        //查找文件
        if (StringUtil.isNotEmpty(cbsId)) {
            CbsServiceEntity cbsServiceEntity = systemService.get(CbsServiceEntity.class, cbsId);
            zyfileLog = systemService.singleResult("from ZyFileLogEntity where businessKey = '" + cbsId + "' and businessType='Qr'");
            if (StringUtil.isNotEmpty(zyfileLog)) {
                String qrcode = zyfileLog.getOldFileName(); //二维码名称
                if (StringUtil.isNotEmpty(qrcode)) {
                    String[] codes = qrcode.split("_");
                    if (codes.length > 0) {
                        boolean datecr = DateUtils.validateTime(sf.format(new Date()), codes[1].toString());
                        if (!datecr) { //大于5分钟，重新生成二维码，删除原二维码
                            boolean f = FileUtils.delete(StaticValue.QrCode_UPLOAD_PATH + qrcode + ".png");
                            if (!f) {//删除文件失败，不会重新生成二维码
                                flag = false;
                            } else {
//                                file = UtilQrCode.generateFile(StaticValue.HTTP_HEADER + "loginWxController.do?goUserCheck&serviceId=" + cbsId+"&qrdate="+Srdate, StaticValue.QrCode_UPLOAD_PATH + cbsServiceEntity.getCode() + "_" + sf.format(new Date()) + ".png");
                                file = UtilQrCode.generateFile(StaticValue.HTTP_HEADER + "loginWxController.do?loginRedirect&type=9&serviceId=" + cbsId + "&qrdate=" + Srdate, StaticValue.QrCode_UPLOAD_PATH + cbsServiceEntity.getCode() + "_" + sf.format(new Date()) + ".png");
                            }
                        } else {
                            file = new File(StaticValue.QrCode_UPLOAD_PATH + qrcode + ".png");
                        }
                        if (flag) {
                            File fileDelete = new File(file.toString());
                            if (fileDelete.exists() || fileDelete.isFile()) {
                                zyfileLog.setFilePath(file.getAbsolutePath());
                                zyfileLog.setOldFileName(cbsServiceEntity.getCode() + "_" + sf.format(new Date()));
                                zyfileLog.setNewFileName(cbsServiceEntity.getCode() + "_" + sf.format(new Date()) + ".png");
                                systemService.save(zyfileLog);
                            }
                        }
                    }
                } else {
//                    file = UtilQrCode.generateFile(StaticValue.HTTP_HEADER + "loginWxController.do?goUserCheck&serviceId=" + cbsId+"&qrdate="+Srdate, StaticValue.QrCode_UPLOAD_PATH + cbsServiceEntity.getCode() + "_" + sf.format(new Date()) + ".png");
                    file = UtilQrCode.generateFile(StaticValue.HTTP_HEADER + "loginWxController.do?loginRedirect&type=9&serviceId=" + cbsId + "&qrdate=" + Srdate, StaticValue.QrCode_UPLOAD_PATH + cbsServiceEntity.getCode() + "_" + sf.format(new Date()) + ".png");
                    File fileDelete = new File(file.toString());
                    if (fileDelete.exists() || fileDelete.isFile()) {
                        zyfileLog.setFilePath(file.getAbsolutePath());
                        zyfileLog.setOldFileName(cbsServiceEntity.getCode() + "_" + sf.format(new Date()));
                        zyfileLog.setNewFileName(cbsServiceEntity.getCode() + "_" + sf.format(new Date()) + ".png");
                        systemService.save(zyfileLog);
                    }
                }

            } else {
//                file = UtilQrCode.generateFile(StaticValue.HTTP_HEADER + "loginWxController.do?goUserCheck&serviceId=" + cbsId+"&qrdate="+Srdate, StaticValue.QrCode_UPLOAD_PATH + cbsServiceEntity.getCode() + "_" + sf.format(new Date()) + ".png");
                file = UtilQrCode.generateFile(StaticValue.HTTP_HEADER + "loginWxController.do?loginRedirect&type=9&serviceId=" + cbsId + "&qrdate=" + Srdate, StaticValue.QrCode_UPLOAD_PATH + cbsServiceEntity.getCode() + "_" + sf.format(new Date()) + ".png");
                File fileDelete = new File(file.toString());
                if (fileDelete.exists() || fileDelete.isFile()) {
                    zyfileLog = new ZyFileLogEntity();
                    zyfileLog.setBusinessType("Qr"); //二维码类型
                    zyfileLog.setBusinessKey(cbsId);
                    zyfileLog.setFilePath(file.getAbsolutePath());
                    zyfileLog.setOldFileName(cbsServiceEntity.getCode() + "_" + sf.format(new Date()));
                    zyfileLog.setNewFileName(cbsServiceEntity.getCode() + "_" + sf.format(new Date()) + ".png");
                    systemService.save(zyfileLog);
                }
            }
        }
        return zyfileLog;
    }

    @Override
    public List<String> queryYearsInService() {
        List<String> years = new ArrayList<String>();
        List<Map<String, Object>> mapList = findForJdbc("SELECT YEAR (create_date) AS _year FROM cbs_service GROUP BY _year ORDER BY _year DESC");
        for (Map<String, Object> map : mapList) {
            years.add(map.get("_year").toString());
        }
        return years;
    }

    /**
     * 院线服务情况报表 - 列表数据
     *
     * @param mold        类型 （1：咨询数量  2：上门服务数量 3：远程服务数量）
     * @param year        年份
     * @param intheaterId 院线
     * @param bp          BP
     * @return
     */
    @Override
    public Map<String, List<Map<String, Integer>>> getIpProblemReportData(String mold, String year, String intheaterId, String bp) {
        //存储结果，List<类型，List<map<月份，数据>>>

        List SnList = new ArrayList(); //存放查询出来的SN
        if (StringUtil.isNotEmpty(bp) || StringUtil.isNotEmpty(intheaterId)) {
            StringBuffer sf = new StringBuffer();
            sf.append("select zy.sn from zy_equipment_spare zy where 1=1 ");

            if (StringUtil.isNotEmpty(bp)) {
                String[] bps = bp.split(",");
                String ss = "'" + bps[0] + "'";
                for (int i = 1; i < bps.length; i++) {
                    ss += ",'" + bps[i] + "'";
                }
                sf.append("AND zy.bp_no in (").append(ss).append(") ");  /*BP*/
            }
            if (StringUtil.isNotEmpty(intheaterId)) {
                String[] intheaterIds = intheaterId.split(",");
                String ss = "'" + intheaterIds[0] + "'";
                for (int i = 1; i < intheaterIds.length; i++) {
                    ss += ",'" + intheaterIds[i] + "'";
                }
                sf.append("AND zy.cinema in (").append(ss).append(") ");  /*BP*/
            }

            //查询
            List<Map<String, Object>> mapList = super.findForJdbc(sf.toString());

            for (Map<String, Object> map : mapList) {
                SnList.add(map.get("sn"));
            }
        }


        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "	MONTH (cbs.create_date) AS 'month',\n" +
                "	type.`name`,\n" +
                "	cbs.sn,cbs.service_provider_code\n" +
                "FROM\n" +
                "	cbs_service cbs\n" +
                "LEFT JOIN cbs_service_type type ON type.id = cbs.service_type_1\n" +
                "WHERE\n" +
                "	YEAR (cbs.create_date) = ").append(year).append(" and type.`name` is not null");   /*年度*/

        List<Map<String, Object>> mapList = super.findForJdbc(sql.toString());
        Set set = new HashSet();//用于保存剔除过查询出来重复的sn
        Map<String, List<Map<String, Integer>>> mapType = new HashMap<>();

        for (Map<String, Object> map : mapList) {
            //查询条件不为空的时候
            if (StringUtil.isNotEmpty(bp) || StringUtil.isNotEmpty(intheaterId)) {
                if (SnList.size() > 0 && SnList.contains(map.get("sn"))) {
                    set.add(map.get("sn"));
                    String name = map.get("name").toString(); //类型
                    String s = map.get("month").toString();//获取月份
                    Object m = map.get("service_provider_code"); //第三方服务商
                    if (name.equals("故障维修")) { //如果name是故障维修时，重新将name赋值
                        /*第三方上门服务数量*/
                        if (StringUtil.isNotEmpty(m) && !m.equals(StaticValue.CBS_CONTRACT_repairProvider_4)) {
                            name = "上门服务";
                        }
                        /*远程服务数量*/
                        if (null == m || m.equals(StaticValue.CBS_CONTRACT_repairProvider_4)) {
                            name = "远程服务";
                        }
                    }

                    if (mapType.containsKey(name)) {
                        //List<map<月份，数据>>
                        List<Map<String, Integer>> mapList1 = mapType.get(name);
                        for (Map<String, Integer> stringIntegerMap : mapList1) {
                            if (stringIntegerMap.containsKey(s)) {
                                stringIntegerMap.put(s, stringIntegerMap.get(s) + 1);
                            } else {
                                stringIntegerMap.put(s, 1);
                            }
                        }
                    } else {
                        //List<map<月份，数据>>
                        List<Map<String, Integer>> mapList1 = new ArrayList<>();
                        Map<String, Integer> mapMonth = new HashMap<>();
                        mapMonth.put(s, 1);
                        mapList1.add(mapMonth);
                        mapType.put(name, mapList1);
                    }
                }
            } else { //查询条件为空的时候
                set.add(map.get("sn"));
                String name = map.get("name").toString(); //类型
                String s = map.get("month").toString();//获取月份
                Object m = map.get("service_provider_code"); //第三方服务商
                if (name.equals("故障维修")) { //如果name是故障维修时，重新将name赋值
                    /*第三方上门服务数量*/
                    if (StringUtil.isNotEmpty(m) && !m.equals(StaticValue.CBS_CONTRACT_repairProvider_4)) {
                        name = "上门服务";
                    }
                    /*远程服务数量*/
                    if (null == m || m.equals(StaticValue.CBS_CONTRACT_repairProvider_4)) {
                        name = "远程服务";
                    }
                }

                if (mapType.containsKey(name)) {
                    //List<map<月份，数据>>
                    List<Map<String, Integer>> mapList1 = mapType.get(name);
                    for (Map<String, Integer> stringIntegerMap : mapList1) {
                        if (stringIntegerMap.containsKey(s)) {
                            stringIntegerMap.put(s, stringIntegerMap.get(s) + 1);
                        } else {
                            stringIntegerMap.put(s, 1);
                        }
                    }
                } else {
                    //List<map<月份，数据>>
                    List<Map<String, Integer>> mapList1 = new ArrayList<>();
                    Map<String, Integer> mapMonth = new HashMap<>();
                    mapMonth.put(s, 1);
                    mapList1.add(mapMonth);
                    mapType.put(name, mapList1);
                }
            }
        }
//        System.out.println(mapType);
        return mapType;
    }

    @Override
    public Map<String, Object> getStatusTableData(String intheaterId, String bp) {
        Map<String, Object> maps = new HashMap<>();

        StringBuffer tjsfTJ = new StringBuffer();
        if (StringUtil.isNotEmpty(bp)) {
            String[] bps = bp.split(",");
            String ss = "'" + bps[0] + "'";
            for (int i = 1; i < bps.length; i++) {
                ss += ",'" + bps[i] + "'";
            }

            tjsfTJ.append("AND zy.bp_no in (" + ss + ") ");  /*BP*/
        }
        if (StringUtil.isNotEmpty(intheaterId)) {
            String[] tjsfIntheaters = intheaterId.split(",");
            String ss = "'" + tjsfIntheaters[0] + "'";
            for (int i = 1; i < tjsfIntheaters.length; i++) {
                ss += ",'" + tjsfIntheaters[i] + "'";
            }
            tjsfTJ.append("AND zy.cinema in (" + ss + ") ");  //院线
        }


        String sf = "SELECT cbs.status,cbs.SPEED_STATUS FROM cbs_service cbs " +
                "LEFT JOIN cbs_service_type type ON type.id = cbs.service_type_1 " +
                "LEFT JOIN zy_equipment_spare zy ON zy.sn = cbs.sn " +
                "WHERE zy.server_state = '" + StaticValue.DIC_SERVER_STATE_1001 + " ' AND type.`name` = '故障维修' AND zy.is_spare = '0'  ";
        List<Map<String, Object>> mapList = super.findForJdbc(sf + tjsfTJ);

        int s = 0, s1 = 0;
        for (Map<String, Object> map : mapList) {
            if (StaticValue.dcl.equals(map.get("status").toString()) || StaticValue.ysl.equals(map.get("status").toString())) {
                s++;
            }
            if (StaticValue.clz.equals(map.get("status").toString()) && StaticValue.gcsssz.equals(map.get("SPEED_STATUS").toString())) {
                s1++;
            }
        }
        /*故障待处理数量*/
        maps.put("gnum", s);
        /*现场处理中数量*/
        maps.put("xnum", s1);

        /*备件在途数量*/
        StringBuffer sf2 = new StringBuffer("SELECT COUNT(distinct(de.cr_code)) as nums FROM cbs_service cbs " +
                "LEFT JOIN cbs_service_type type ON type.id = cbs.service_type_1 " +
                "LEFT JOIN zy_equipment_spare zy ON zy.sn = cbs.sn ");
        sf2.append(" INNER JOIN cbs_deliver de ON de.cr_code = cbs.`code` ");
        sf2.append(" WHERE type.`name` = '故障维修' AND zy.is_spare = '0' ");
        sf2.append(" AND zy.server_state = '" + StaticValue.DIC_SERVER_STATE_1001 + " ' ");
        sf2.append(" AND cbs.status != '" + StaticValue.ywc + "'");
        sf2.append(" AND cbs.status != '" + StaticValue.yqx + "'");
        sf2.append(" and de.`status` != '" + StaticValue.qs + "'");
        List<Map<String, Object>> mapList1 = super.findForJdbc(sf2.append(tjsfTJ).toString());
        maps.put("bnum", mapList1.get(0).get("nums"));

        /*正常放映数*/
        //1.查服务单中所有的不重复的设备号
        String sql1 = "SELECT COUNT(distinct(cbs.sn)) as num from cbs_service cbs LEFT JOIN zy_equipment_spare zy ON zy.sn = cbs.sn where 1=1 AND zy.is_spare = '0' and (cbs.`status` ='" + StaticValue.dcl + "' or cbs.`status` ='" + StaticValue.ysl + "' or cbs.`status` ='" + StaticValue.clz + "'  ) AND zy.server_state = '" + StaticValue.DIC_SERVER_STATE_1001 + "' ";
        List<Map<String, Object>> maps1 = systemService.findForJdbc(sql1 + tjsfTJ);
        //2.查项目中所有设备的数量 is_spare = 0 为设置数量，is_spare = 1为服务器数量
        String sql2 = "SELECT COUNT(distinct(zy.sn)) as sum from zy_equipment_spare zy where 1=1 AND zy.is_spare = '0' AND zy.server_state = '" + StaticValue.DIC_SERVER_STATE_1001 + "'  ";
        List<Map<String, Object>> maps2 = systemService.findForJdbc(sql2 + tjsfTJ);
        if (maps1.size() > 0) {
            int num = maps1.get(0).containsKey("num") ? Integer.parseInt(maps1.get(0).get("num").toString()) : 0;
            int sum = maps2.get(0).containsKey("sum") ? Integer.parseInt(maps2.get(0).get("sum").toString()) : 0;
            maps.put("znums", sum - num);
        }


        return maps;
    }

    @Override
    public Map<String, List<Map<String, Object>>> getFacilitiesZtTableData(String cdateBegin, String cdateEnd, String intheatername, String bp) {
        //存储二维数据
        Map<String, List<Map<String, Object>>> map = new HashMap<>();

        List<String> listMonth = null;/*存储查找两个时间段之间的月份*/
        try {
            if (StringUtil.isNotEmpty(cdateBegin) && StringUtil.isNotEmpty(cdateEnd)) {
                listMonth = DateUtils.getMonthBetween(cdateBegin, cdateEnd);
                List maplists = new ArrayList();
                Map maps = new HashMap();
                maps.put("year", listMonth); //年-月
                maplists.add(maps);
                map.put("sy", maplists);

                //查找条件下所有的机型
                String stringType = "SELECT zyt.type_desc, zyt.id as Tid FROM zy_equipment_type zyt WHERE parent_id = '402883f3545bca4101545be730e0000b' GROUP BY zyt.type_desc ORDER BY zyt.type_desc ";
                List<Map<String, Object>> mapList = super.findForJdbc(stringType); //所有的机型


                //查询条件
                StringBuffer tiaojianSql = new StringBuffer();
                if (StringUtil.isNotEmpty(intheatername)) {
                    tiaojianSql.append(" AND (");
                    for (int i = 0; i < intheatername.split(",").length; i++) {
                        if (i == 0) {
                            tiaojianSql.append(" zys.cinema = '").append(intheatername.split(",")[i]).append("' ");  //院线
                        } else {
                            tiaojianSql.append("or zys.cinema = '").append(intheatername.split(",")[i]).append("' "); //院线
                        }
                    }
                    tiaojianSql.append(" ) ");
                }
                if (StringUtil.isNotEmpty(bp)) {
                    tiaojianSql.append(" AND (");
                    for (int i = 0; i < bp.split(",").length; i++) {
                        if (i == 0) {
                            tiaojianSql.append(" zys.bp_no = '").append(bp.split(",")[i]).append("' ");  //BP
                        } else {
                            tiaojianSql.append("or zys.bp_no = '").append(bp.split(",")[i]).append("' "); //BP
                        }
                    }
                    tiaojianSql.append(" ) ");
                }

                //基本查询sql，然后逻辑再按照机型和时间分组
                String sf = "SELECT date_format( zys.REPAIR_END_DATE, '%Y-%m-%d' ) AS date, zyt.parent_id, date_format(zys.install_date, '%Y-%m-%d') AS installdate FROM zy_equipment_spare zys LEFT JOIN zy_equipment_type zyt ON zys.type = zyt.id WHERE zys.is_spare = '0' AND zys.server_state = '1001 ' and zys.REPAIR_END_DATE IS NOT NULL AND zyt.parent_id IS NOT NULL AND zys.install_date IS NOT NULL";
                List<Map<String, Object>> mapList1 = super.findForJdbc(sf + tiaojianSql);
                for (Map<String, Object> stringObjectMap : mapList1) {
                    String date = stringObjectMap.get("date").toString();
                    String parent_id = stringObjectMap.get("parent_id").toString();
                    String installdate = stringObjectMap.get("installdate").toString();
                    for (String s : listMonth) {
                        String[] months = s.split("-");
                        String laseDay = DateUtils.getLastDayOfMonth(Integer.parseInt(months[0]), Integer.parseInt(months[1]));

                        //计算在保数量
                        if (DateUtils.ComparDate(date, laseDay) && !DateUtils.ComparDate(installdate, laseDay)) {
                            if (map.containsKey(s)) {
                                List<Map<String, Object>> list = map.get(s);
                                for (Map<String, Object> objectMap : list) {
                                    if (objectMap.containsKey("在保数量")) {
                                        for (String s1 : objectMap.keySet()) {
                                            if (s1.equals("在保数量")) {
                                                objectMap.put(s1, Integer.parseInt(objectMap.get(s1).toString()) + 1);
                                            }
                                        }
                                    } else {
                                        objectMap.put("在保数量", 1);
                                    }
                                }

                            } else {
                                List<Map<String, Object>> list = new ArrayList<>();
                                Map<String, Object> m = new HashMap<>();
                                m.put("在保数量", 1);
                                list.add(m);
                                map.put(s, list);
                            }
                        }


                        for (Map<String, Object> stringMap : mapList) { //机型
                            String Typeid = stringMap.get("Tid").toString(); //机型id
                            String descType = stringMap.get("type_desc").toString(); //机型

                            //小于等于该月最后一天，且机型相等的情况
                            if (parent_id.equals(Typeid) && !DateUtils.ComparDate(date, laseDay)) {
                                if (map.containsKey(s)) {
                                    List<Map<String, Object>> list = map.get(s);
                                    for (Map<String, Object> objectMap : list) {
                                        if (objectMap.containsKey(descType)) {
                                            objectMap.put(descType, Integer.parseInt(objectMap.get(descType).toString()) + 1);
                                        } else {
                                            objectMap.put(descType, 1);
                                        }
                                    }
                                } else {
                                    List<Map<String, Object>> list = new ArrayList<>();
                                    Map<String, Object> m = new HashMap<>();
                                    m.put(descType, 1);
                                    list.add(m);
                                    map.put(s, list);
                                }
                            }
                        }

                    }

                }

                Map<String, Object> map1 = new HashMap<>();
                map1.put("type_desc", "在保数量");
                map1.put("Tid", "");
                mapList.add(map1);
                map.put("maptype", mapList); //机型
            }

        } catch (ParseException e) {
            e.printStackTrace();
        }


        return map;
    }


    @Override
    public Map<String, List<Map<String, Object>>> getFacilitiesCbTableData(String cdateBegin, String cdateEnd, String intheatername, String bp) {
        String str = "";//根据年月获取最后一天
        String[] dates = new String[2];
        //if (StringUtil.isNotEmpty(cdateBegin)) {} //开始时间 ，不做删选

        //查询条件sql
        StringBuffer tjSql = new StringBuffer();
        if (StringUtil.isNotEmpty(intheatername)) {  //院线
            String[] intheaterIds = intheatername.split(",");
            String ss = "'" + intheaterIds[0] + "'";
            for (int i = 1; i < intheaterIds.length; i++) {
                ss += ",'" + intheaterIds[i] + "'";
            }
            tjSql.append("AND zys.cinema in (").append(ss).append(") ");
        }
        if (StringUtil.isNotEmpty(bp)) {
            String[] bps = bp.split(",");
            String ss = "'" + bps[0] + "'";
            for (int i = 1; i < bps.length; i++) {
                ss += ",'" + bps[i] + "'";
            }
            tjSql.append("AND zys.bp_no in (" + ss + ") ");  /*BP*/
        }


        Map<String, List<Map<String, Object>>> mapListMap = new HashMap<>();
        //查询所有的机型(大类)
        String sqlType = "SELECT zyt.type_desc as Tdesc, zyt.id as Tid FROM zy_equipment_type zyt WHERE parent_id = '402883f3545bca4101545be730e0000b'  GROUP BY zyt.type_desc ORDER BY zyt.type_desc ";
        List<Map<String, Object>> TypeMapList = super.findForJdbc(sqlType);
        if (StringUtil.isNotEmpty(cdateEnd)) {
            dates = cdateEnd.split("-");
            str = DateUtils.getLastDayOfMonth(Integer.parseInt(dates[0]), Integer.parseInt(dates[1]));

            if (TypeMapList.size() > 0) {
                //根据机型和年限，查满足要求的个数
                String sql = "SELECT date_format( zys.repair_end_date, '%Y-%m-%d' ) as repair_end_date, zyt.parent_id, date_format( zys.install_date, '%Y' ) as install_date FROM zy_equipment_spare zys LEFT JOIN zy_equipment_type zyt ON zyt.id = zys.type WHERE 1 = 1 AND zys.is_spare = '0' AND zys.server_state = '1001' and zys.install_date is not NULL ";
                List<Map<String, Object>> SumMapList = super.findForJdbc(sql + tjSql);

                for (Map<String, Object> map : SumMapList) {
                    String repair_end_date = map.get("repair_end_date").toString();
                    String parent_id = map.get("parent_id").toString();
                    String install_date = map.get("install_date").toString();

                    //机型
                    for (Map<String, Object> stringObjectMap : TypeMapList) {
                        String Typeid = stringObjectMap.get("Tid").toString(); //机型id
                        String descType = stringObjectMap.get("Tdesc").toString(); //机型

                        if (Typeid.equals(parent_id) && !DateUtils.ComparDate(repair_end_date, str)) {
                            if (Integer.parseInt(dates[0].toString()) - Integer.parseInt(install_date) >= 10) {
                                if (mapListMap.containsKey(descType)) {
                                    List<Map<String, Object>> list = mapListMap.get(descType);
                                    for (Map<String, Object> stringStringMap : list) {
                                        if (stringStringMap.containsKey("10")) {
                                            stringStringMap.put("10", Integer.parseInt(stringStringMap.get("10").toString()) + 1);
                                        } else {
                                            stringStringMap.put("10", 1);
                                        }
                                    }

                                } else {
                                    List<Map<String, Object>> list1 = new ArrayList<>();
                                    Map<String, Object> map1 = new HashMap<>();
                                    map1.put("10", 1);
                                    list1.add(map1);
                                    mapListMap.put(descType, list1);
                                }
                            } else {

                                for (Object o : ListYear) {

                                    if ((Integer.parseInt(dates[0].toString()) - Integer.parseInt(install_date) == Integer.parseInt(o.toString()))) {
                                        if (mapListMap.containsKey(descType)) {
                                            List<Map<String, Object>> list = mapListMap.get(descType);
                                            for (Map<String, Object> stringStringMap : list) {
                                                if (stringStringMap.containsKey(o.toString())) {
                                                    stringStringMap.put(o.toString(), Integer.parseInt(stringStringMap.get(o.toString()).toString()) + 1);
                                                } else {
                                                    stringStringMap.put(o.toString(), 1);
                                                }
                                            }

                                        } else {
                                            List<Map<String, Object>> list1 = new ArrayList<>();
                                            Map<String, Object> map1 = new HashMap<>();
                                            map1.put(o.toString(), 1);
                                            list1.add(map1);
                                            mapListMap.put(descType, list1);
                                        }
                                    }
                                }
                            }
                        }
                    }

                }
            }

        }

        return mapListMap;
    }

    /**
     * 院线机型配比
     *
     * @param cdateBegin    开始月份时间
     * @param cdateEnd      结束月份时间
     * @param intheatername 院线
     * @param bp            BP
     * @return
     */
    @Override
    public Map<String, String> getEsHighChartsOfCbData(String cdateBegin, String cdateEnd, String intheatername, String bp) {
        Map<String, String> map = new HashMap<String, String>();
        String sf = "SELECT count(zys.id) AS num, zyt2.type_desc AS type FROM zy_equipment_spare zys LEFT JOIN zy_equipment_type zyt ON zys.type = zyt.id  LEFT JOIN zy_equipment_type zyt2 ON zyt.parent_id = zyt2.id  WHERE 1 = 1  AND zys.is_spare = '0' ";
        String all = "AND zys.server_state = '" + StaticValue.DIC_SERVER_STATE_1001 + "' and zyt2.parent_id = '402883f3545bca4101545be730e0000b' group by zyt.parent_id ";
        String tjSql = "";  //查询条件sql
        if (StringUtil.isNotEmpty(intheatername)) {  //院线
            tjSql += " AND (";
            for (int i = 0; i < intheatername.split(",").length; i++) {
                if (i == 0) {
                    tjSql += " zys.cinema = '" + intheatername.split(",")[i] + "' ";
                } else {
                    tjSql += "or zys.cinema = '" + intheatername.split(",")[i] + "' ";
                }
            }
            tjSql += " ) ";
        }
        if (StringUtil.isNotEmpty(bp)) {
            StringBuffer sb = new StringBuffer();
            sb.append(" AND (");
            for (int i = 0; i < bp.split(",").length; i++) {
                if (i == 0) {
                    sb.append(" zys.bp_no = '").append(bp.split(",")[i]).append("' ");  //BP
                } else {
                    sb.append("or zys.bp_no = '").append(bp.split(",")[i]).append("' "); //BP
                }
            }
            sb.append(" ) ");
            tjSql += sb.toString();
        }

        List<Map<String, Object>> NumList = super.findForJdbc(sf + tjSql + all);
        for (int i = 0; i < NumList.size(); i++) {
            map.put(NumList.get(i).get("type").toString(), NumList.get(i).get("num").toString());
        }
//        /*一代机数量*/
//        Integer ydjNum = 0;
//        String ydj = "AND zys.server_state = '" + StaticValue.DIC_SERVER_STATE_1001 + "' AND zyt.parent_id IN ( SELECT id FROM zy_equipment_type WHERE type_desc = '一代机' )";
//        List<Map<String, Object>> ydjNumList = super.findForJdbc(sf + ydj);
//        if (ydjNumList.size() > 0) {
//            if (StringUtil.isNotEmpty(ydjNumList.get(0).get("num"))) {
//                ydjNum = Integer.parseInt(ydjNumList.get(0).get("num").toString());
//                map.put("ydj", ydjNumList.get(0).get("num").toString());
//            }
//        }
//        /*激光机数量*/
//        /*机型分类为RGB激光和荧光粉激光*/
//        String jg1 = "AND zys.server_state = '" + StaticValue.DIC_SERVER_STATE_1001 + "' AND zyt.parent_id IN ( SELECT id FROM zy_equipment_type WHERE type_desc = 'RGB激光' OR type_desc = '荧光粉激光' )";
//        /*是否激光改造”为“是*/
//        String jg2 = "AND zys.server_state = '" + StaticValue.DIC_SERVER_STATE_1001 + "' AND zyt.parent_id NOT IN ( SELECT id FROM zy_equipment_type WHERE type_desc = 'rgb激光' OR type_desc = '荧光粉激光' ) AND zys.laser_reconstruction = 1 ";
//        Integer jgnum = 0;
//        List<Map<String, Object>> jgNum1 = super.findForJdbc(sf + jg1);
//        if (jgNum1.size() > 0) {
//            if (StringUtil.isNotEmpty(jgNum1.get(0).get("num"))) {
//                jgnum = jgnum + Integer.parseInt(jgNum1.get(0).get("num").toString());
//            }
//        }
//        List<Map<String, Object>> jgNum2 = super.findForJdbc(sf + jg2);
//        if (jgNum2.size() > 0) {
//            if (StringUtil.isNotEmpty(jgNum2.get(0).get("num"))) {
//                jgnum = jgnum + Integer.parseInt(jgNum2.get(0).get("num").toString());
//            }
//        }
//        map.put("jg", jgnum.toString());
//
//        /*二代机数量*/
//        Integer edSum = 0;
//        List<Map<String, Object>> edNum = super.findForJdbc(sf + " AND zys.server_state = '" + StaticValue.DIC_SERVER_STATE_1001 + "'");
//        if (edNum.size() > 0) {
//            if (StringUtil.isNotEmpty(edNum.get(0).get("num"))) {
//                edSum = Integer.parseInt(edNum.get(0).get("num").toString()) - ydjNum - jgnum;
//                map.put("ed", edSum.toString());
//            }
//        }

        return map;
    }

    @Override
    public AjaxJson changePerson(HttpServletRequest request) {
        TSUser user1 = ResourceUtil.getSessionUserName(); //当前用户
        AjaxJson j = new AjaxJson();
        boolean flag = true;
        String msg = "转派成功";

        String cbserviceId = oConvertUtils.getString(request.getParameter("cbserviceId")); //服务单id
        String userId = oConvertUtils.getString(request.getParameter("id"));//新责任人id
        String desption = oConvertUtils.getString(request.getParameter("desption")); //转派描述
        try {
            //user1操作人，是否是当前责任人
            if (StringUtil.isNotEmpty(cbserviceId) && StringUtil.isNotEmpty(userId)) {
                CbsServiceEntity serviceEntity = systemService.get(CbsServiceEntity.class, cbserviceId);
                TSUser user = zyCommonService.singleResult("from TSUser where id = '" + userId + "'"); //新责任人
                String sql = "UPDATE cbs_service set person_liable='" + userId + "' where id='" + cbserviceId + "'";
                this.updateBySqlString(sql);

                //存入操作处理日志描述
                CbsHandleLogEntity cbsHandleLogEntity = new CbsHandleLogEntity();
                cbsHandleLogEntity.setBusinessId(cbserviceId);
                cbsHandleLogEntity.setDescription(desption);
//              cbsHandleLogEntity.setCreateName(user.getRealName());   18/5/18 修改bug 。修改责任人，为操作转派的人
                cbsHandleLogEntity.setLogType(3);
                cbsHandleLogServiceI.save(cbsHandleLogEntity);
                /*操作流程*/
                CbsServiceAssignListEntity cbsServiceAssignListEntity = new CbsServiceAssignListEntity();
                cbsServiceAssignListEntity.setServiceId(serviceEntity);
                cbsServiceAssignListEntity.setAssignType(2);
                cbsServiceAssignListEntity.setTurnUserId(user);
                cbsServiceAssignListService.save(cbsServiceAssignListEntity);


                if (null != user) {
                    if (serviceEntity != null) {
                        SimpleDateFormat sdf = new SimpleDateFormat("MMdd");
                        Date date = new Date();
                        String time = sdf.format(date);
                        StringBuffer subject = new StringBuffer();
                        subject.append("巴可服务单:");
                        if (StringUtil.isNotEmpty(serviceEntity.getLevel())) {
                            subject.append("<" + serviceEntity.getLevel() + ">-");
                        }
                        if (StringUtil.isNotEmpty(serviceEntity.getServiceType1())) {
                            subject.append("<" + serviceEntity.getServiceType1().getName() + ">-");
                        }
                        if (StringUtil.isNotEmpty(serviceEntity.getCode())) {
                            subject.append("<" + serviceEntity.getCode() + ">");
                        }
                        if (StringUtil.isNotEmpty(serviceEntity.getZyCinemaName())) {
                            subject.append("<" + serviceEntity.getZyCinemaName() + ">-");
                        }
                        subject.append(time);

                        Map<String, Object> paramMap = new HashMap<String, Object>();
                        paramMap.put("user", user.getRealName());
                        paramMap.put("code", serviceEntity.getCode());
                        if (serviceEntity.getZyEquipmentSpareId() != null && serviceEntity.getZyEquipmentSpareId().getType() != null) {
                            paramMap.put("typedesc", serviceEntity.getZyEquipmentSpareId().getType().getTypeDesc());
                        } else {
                            paramMap.put("typedesc", "");
                        }
                        paramMap.put("sn", serviceEntity.getSn());
                        paramMap.put("des", desption);
                        paramMap.put("cina", serviceEntity.getZyCinemaName());
                        paramMap.put("username", serviceEntity.getContactsName());
                        paramMap.put("phone", serviceEntity.getContactsPhone());
                        paramMap.put("pname", user1.getRealName());
                        paramMap.put("pphone", user1.getMobilePhone());
                        ZyEmailEntity email = new ZyEmailEntity();
                        email.setEmailReceiver(user.getEmail());
                        email.setEmailSubject(subject.toString());
                        SendMailUtil.sendFtlMail(email, "mail_changePerson.ftl", paramMap, systemService);
                    }

                }

                /*添加日志*/
                cbsServiceLogServiceI.addServiceLog(serviceEntity, StaticValue.Close_Service_Log_Type_2, "服务单将责任人：" + serviceEntity.getPersonLiable().getRealName() + "更换为：" + user.getRealName() + (StringUtil.isNotEmpty(desption.toString()) ? (" 备注：" + desption) : ""), null);
                systemService.addLog("转派成功", Globals.Log_Type_INSERT, Globals.Log_Leavel_INFO);
            }
        } catch (Exception e) {
            msg = "转派异常";
            flag = false;
            e.printStackTrace();
            System.err.println("事务出现异常,回滚事务！");
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//此处采用手动回滚
        }
        j.setMsg(msg);
        j.setSuccess(flag);
        return j;
    }

    @Override
    public AjaxJson closeServiceList(HttpServletRequest request, HttpServletResponse response) {
        AjaxJson ajaxJson = new AjaxJson();
        String msg = "关单成功！";
        boolean flag = true;
        String id = request.getParameter("id");  //服务单id
        String description = request.getParameter("description");  //服务单id
        try {
            TSUser user = ResourceUtil.getSessionUserName();
            if (StringUtil.isNotEmpty(id)) {
                request.setAttribute("id", id);
                CbsServiceEntity cbsServiceEntity = systemService.get(CbsServiceEntity.class, id);
                if (!cbsServiceEntity.getStatus().equals(StaticValue.ywc)) {
                    cbsServiceEntity.setCloseTime(new Date());  //关单时间
                    cbsServiceEntity.setCloseUser(user); //关单人
                    cbsServiceEntity.setStatus(StaticValue.ywc);
                    //如果第三方进度不为"HD待分配"、“拒绝”或“已完成”,则更新第三方进度为“已完成”
                    if (!cbsServiceEntity.getSpeedStatus().equals(StaticValue.fwywc) && !cbsServiceEntity.getSpeedStatus().equals(StaticValue.dsfyjj) && !cbsServiceEntity.getSpeedStatus().equals(StaticValue.dfp)) {
                        cbsServiceEntity.setSpeedStatus(StaticValue.fwywc);
                    }
                    this.saveOrUpdate(cbsServiceEntity);

                    List<TSUser> wxUser = userService.getUserByMobilePhone(cbsServiceEntity.getContactsPhone());
                    if (StringUtil.isNotEmpty(wxUser) && wxUser.size() > 0) {
                        if (StringUtil.isNotEmpty(wxUser) && wxUser.get(0).getOpenId() != null && !wxUser.get(0).getOpenId().equals("")) {
                            /*发送微信提醒*/
                            //调用微信接口发送
                            List<Map<String, String>> list = new ArrayList<>();
                            String msContent = "尊敬的客户：您的服务单<" + cbsServiceEntity.getCode() + ">的服务已经完成。请您在<个人中心>-<我的服务单>中对本次服务进行评价。【中影巴可客服部】";
                            Map<String, String> map = new HashMap<>();
                            map.put("openid", wxUser.get(0).getOpenId());
                            map.put("message", msContent);
                            list.add(map);
                            wxCommonService.sendMsgForWx(list);

                        }
                    }
                    //判断服务单完工时，是否需要自动创建回访
                    cbsInspectionEvaluateServiceI.assessService(cbsServiceEntity.getId());
                    //服务单日志
                    cbsServiceLogServiceI.addServiceLog(cbsServiceEntity, StaticValue.Close_Service_Log_Type_2, "服务单关单成功" + (StringUtil.isNotEmpty(description) ? (":" + description) : ""), null);
                    //系统日志
                    systemService.addLog("服务单关单" + (StringUtil.isNotEmpty(description) ? description : ""), Globals.Log_Type_OTHER, Globals.Log_Leavel_INFO);
                    ajaxJson.setObj(cbsServiceEntity.getCode());
                } else {
                    flag = false;
                    msg = "关单失败，服务单状态已变更，请刷新后再操作。";
                }
            }
        } catch (Exception e) {
            msg = "关单异常";
            flag = false;
            e.printStackTrace();
            System.err.println("事务出现异常,回滚事务！");
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//此处采用手动回滚
        }
        ajaxJson.setMsg(msg);
        ajaxJson.setSuccess(flag);
        return ajaxJson;
    }

    /*@Override
    public AjaxJson checkForEditServiceReport(String serviceId) {
        AjaxJson j = new AjaxJson();
        String message = "";
        boolean success = true;
        try {
            if(StringUtil.isNotEmpty(serviceId)){
                TSUser user = ResourceUtil.getSessionUserName();
                if(null != user && StringUtil.isNotEmpty(user.getId())){
                    if(!userService.hasRoles(user.getUserName(),new String[]{StaticValue.CBS_HD_ROLE_CODE, StaticValue.RULE_CODE_THIRDPARTY_HEADER, StaticValue.RULE_CODE_THIRDPARTY_ENGINEER})) {
                        *//**
     * 检查条件1：角色 不包含 HD，第三方商务，第三方工程师 中任意一种不能补填
     *//*
                        success = false;
                        message = "当前角色不能编辑完工单";
                    }else{
                        *//**
     * 检查条件2：服务单状态 ：已完成 or 第三方服务进度已完成
     *//*
                        CbsServiceEntity cbsService = systemService.getEntity(CbsServiceEntity.class, serviceId);
                        if(null != cbsService){
                            //服务单状态既不是 已完成 也不是服务状态已完成
                            if(!StaticValue.ywc.equals(cbsService.getStatus()) && !StaticValue.fwywc.equals(cbsService.getSpeedStatus())){
                                success = false;
                                message = "当前服务单状态不能编辑完工单";
                            }else{
                                */

    /**
     * 检查条件3：距离完工单提交不超过15天（包含）
     *//*
                                if(StringUtil.isNotEmpty(cbsService.getCloseTime())){
                                    Calendar cal = Calendar.getInstance();
                                    cal.setTime(cbsService.getCloseTime());
                                    cal.add(Calendar.DAY_OF_YEAR, 15);
                                    Long closeDayAdded = DateUtils.date_sdf.parse(DateUtils.date_sdf.format(cal.getTime())).getTime();
                                    Long today = DateUtils.date_sdf.parse(DateUtils.date_sdf.format(DateUtils.getDate())).getTime();
                                    if(closeDayAdded < today){
                                        success = false;
                                        message = "距离完工单提交超过了15天（包含15天）";
                                    }
                                }else{//should not happend
                                    success = false;
                                    message = "没有设置关单时间";
                                }
                            }
                        }else{//should not happend
                            success = false;
                            message = "没有找到当前服务单";
                        }
                    }
                }else{//should not happend
                    success = false;
                    message = "登陆用户为空";
                }
            }else{
                message = "参数不正确";
                success = false;
            }
        } catch (Exception e) {
            e.printStackTrace();
            success = false;
            message = "验证出现异常，联系管理员";
        }
        j.setSuccess(success);
        j.setMsg(message);
        return j;
    }*/
    @SuppressWarnings("Duplicates")
    @Override
    public AjaxJson checkForEditServiceReport(String serviceId) {
        AjaxJson j = new AjaxJson();
        String message = "";
        boolean success = true;
        try {
            if (StringUtil.isNotEmpty(serviceId)) {
                TSUser user = ResourceUtil.getSessionUserName();
                if (null != user && StringUtil.isNotEmpty(user.getId())) {
                    String userRolesStr = userService.getUserRole(user);
                    List<String> userRoles = new ArrayList<>();
                    if (StringUtil.isNotEmpty(userRolesStr)) {
                        userRoles = Arrays.asList(userRolesStr.split(","));
                    }
                    if (userRoles.size() > 0) {
                        /**
                         * 检查条件1：角色 包含 HD，第三方商务，第三方工程师，区域工程师 中任意一种
                         */
                        Boolean flag = false;
                        List<String> rightCodes = Arrays.asList(new String[]{
                                StaticValue.CBS_HD_ROLE_CODE,
                                StaticValue.RULE_CODE_THIRDPARTY_HEADER,
                                StaticValue.RULE_CODE_THIRDPARTY_ENGINEER,
                                StaticValue.RULE_CODE_AREA_ENGINEER
                        });

                        for (String roleCode : userRoles) {
                            if (rightCodes.contains(roleCode)) {
                                flag = true;
                                break;
                            }
                        }
                        if (flag) {
                            CbsServiceEntity cbsService = systemService.getEntity(CbsServiceEntity.class, serviceId);
                            if (null != cbsService) {
                                /**
                                 * 检查条件2：服务单状态 ：已完成 or 第三方服务进度已完成
                                 */
                                if (StaticValue.ywc.equals(cbsService.getStatus()) || StaticValue.fwywc.equals(cbsService.getSpeedStatus())) {
                                    /**
                                     * 检查条件3：检查是否有完工单 （状态是 已完成 或者 第三方状态是已完成 也有可能没有完工单）
                                     */
                                    if (StringUtil.isNotEmpty(cbsService.getServiceType1()) && StringUtil.isNotEmpty(cbsService.getServiceType2())) {
                                        flag = false;
                                        if (cbsService.getServiceType1().getName().equals("服务支持")
                                                && (cbsService.getServiceType2().getName().equals("现场检测") || cbsService.getServiceType2().getName().equals("维保检测"))) {
                                            List<Map<String, Object>> rsMapList = systemService.findForJdbc("select id from cbs_scene_check_report where service_id = ? ", serviceId);
                                            if (null != rsMapList && rsMapList.size() > 0) {
                                                flag = true;
                                            }
                                        }
                                        //引擎翻新业务(重写)
                                        else if (cbsService.getServiceType1().getName().equals("增值服务") && cbsService.getServiceType2().getName().equals("引擎翻新")) {
                                            List<Map<String, Object>> rsMapList = systemService.findForJdbc("select id from cbs_engine_accept_report where service_id = ? ", serviceId);
                                            if (null != rsMapList && rsMapList.size() > 0) {
                                                flag = true;
                                            }
                                        }
                                        /*光管翻新业务*/
                                        else if (cbsService.getServiceType1().getName().equals("增值服务") && cbsService.getServiceType2().getName().equals("光管翻新")) {
                                            List<Map<String, Object>> rsMapList = systemService.findForJdbc("select id from CBS_LIGHT_REPORT where service_id = ? ", serviceId);
                                            if (null != rsMapList && rsMapList.size() > 0) {
                                                flag = true;
                                            }
                                        }
                                        /*技术支持*/
                                        else if (cbsService.getServiceType1().getName().equals("服务支持") && cbsService.getServiceType2().getName().equals("技术支持")) {
                                            List<Map<String, Object>> rsMapList = systemService.findForJdbc("select id from CBS_TECHOLOGY_AGREE_REPORT where service_id = ? ", serviceId);
                                            if (null != rsMapList && rsMapList.size() > 0) {
                                                flag = true;
                                            }
                                        }
                                        /*借用*/
                                        else if (cbsService.getServiceType1().getName().equals("故障维修")) {
                                            List<Map<String, Object>> rsMapList = systemService.findForJdbc("select id from cbs_scene_report where service_id = ? ", serviceId);
                                            if (null != rsMapList && rsMapList.size() > 0) {
                                                flag = true;
                                            }
                                        }
                                        if (flag) {
                                            /**
                                             * 检查条件4：除了巴可HD ，距离完工单提交不超过15天（包含）
                                             */
                                            if (!userRoles.contains(StaticValue.CBS_HD_ROLE_CODE)) {
                                                //如果关单时间非空，判断是否超时
                                                if (StringUtil.isNotEmpty(cbsService.getCloseTime())) {
                                                    Calendar cal = Calendar.getInstance();
                                                    cal.setTime(cbsService.getCloseTime());
                                                    cal.add(Calendar.DAY_OF_YEAR, 15);
                                                    Long closeDayAdded = DateUtils.date_sdf.parse(DateUtils.date_sdf.format(cal.getTime())).getTime();
                                                    Long today = DateUtils.date_sdf.parse(DateUtils.date_sdf.format(DateUtils.getDate())).getTime();
                                                    if (closeDayAdded < today) {
                                                        success = false;
                                                        message = "已超时，不可修改完工单";
                                                    }
                                                } else {//should not happend
                                                    success = false;
                                                    message = "补充附件请联系巴可处理";
                                                }
                                            }
                                        } else {
                                            /**
                                             * 如果没有找到对应的完工单
                                             */
                                            success = false;
                                            message = "补充附件请联系巴可处理";
                                        }
                                    } else {
                                        //should not happend
                                        message = "服务单类型为空";
                                        success = false;
                                    }
                                } else {
                                    success = false;
                                    message = "未提交完工单";
                                }
                            } else {//should not happend
                                success = false;
                                message = "没有找到当前服务单";
                            }
                        } else {
                            success = false;
                            message = "当前角色不能编辑完工单";
                        }
                    }
                } else {//should not happend
                    success = false;
                    message = "登陆用户为空";
                }
            } else {
                message = "参数不正确";
                success = false;
            }
        } catch (Exception e) {
            e.printStackTrace();
            success = false;
            message = "验证出现异常，联系管理员";
        }
        j.setSuccess(success);
        j.setMsg(message);
        return j;
    }


    //序列号验证
    @Override
    public boolean snValiDate(String cbsId, String[] s_ns) {
        boolean flag = true;
        TSUser sessionUserName = ResourceUtil.getSessionUserName();
        List<String> snList = systemService.findListbySql("select sn from zy_equipment_spare where is_spare='0';");
        CbsServiceEntity cbsServiceEntity = systemService.getEntity(CbsServiceEntity.class, cbsId);
        List<CbsEquipmentApplyEntity> hql = systemService.findHql("from CbsEquipmentApplyEntity where createName = '" + sessionUserName.getRealName() + "' order by createDate desc");
        List<String> errorList = systemService.findListbySql("select id from cbs_equipment_error_log where is_close = 1 and sn='" + cbsServiceEntity.getSn() + "' and cbs_equipment_apply_id='" + hql.get(0).getId() + "'");
        if (errorList.size() == 0 || StringUtil.isEmpty(errorList.get(0))) {       	
	        CbsEquipmentErrorLogEntity cbsEquipmentErrorLog = new CbsEquipmentErrorLogEntity();
	        //1、如果备件申请的设备序列号不存在，创建异常记录。由系统管理员创建设备序列号后关闭异常记录。
	        if (!snList.contains(cbsServiceEntity.getSn())) {
	            cbsEquipmentErrorLog.setCbsEquipmentApplyId(hql.get(0).getId());
	            cbsEquipmentErrorLog.setSn(cbsServiceEntity.getSn());
	            cbsEquipmentErrorLog.setIsClose(1);
	            cbsEquipmentErrorLog.setAbnormalCause("设备序列号不存在");
	            flag = false;
	            systemService.save(cbsEquipmentErrorLog);
	        } else {
	            List<String> serverStateList = systemService.findListbySql("select server_state from zy_equipment_spare where sn='" + cbsServiceEntity.getSn() + "'");
	            if ("1003".equals(serverStateList.get(0))) {
	                cbsEquipmentErrorLog.setCbsEquipmentApplyId(hql.get(0).getId());
	                cbsEquipmentErrorLog.setSn(cbsServiceEntity.getSn());
	                cbsEquipmentErrorLog.setIsClose(1);
	                cbsEquipmentErrorLog.setAbnormalCause("设备已停止使用");
	                flag = false;
	                systemService.save(cbsEquipmentErrorLog);
	            } else {
	                //3、如果备件序列号在另外一台出保设备上，创建异常记录。由系统管理员手工验证核实后关闭异常记录。
	                List<String> topItemSnList = new ArrayList<String>();
	                List<String> listbySql = null;
	                if (s_ns != null) {
	                    for (int i = 0; i < s_ns.length; i++) {
	                        if (listbySql != null) {
	                            listbySql.clear();
	                        }
	                        listbySql = systemService.findListbySql("select top_item_sn from zy_equipment_spare where sn='" + s_ns[i] + "'");
	                        if (listbySql.size() == 0) {
	                            //4、如果备件序列号不存在系统记录中，创建异常记录。由系统管理员手工验证核实后关闭异常记录。
	                            cbsEquipmentErrorLog.setCbsEquipmentApplyId(hql.get(0).getId());
	                            cbsEquipmentErrorLog.setSn(cbsServiceEntity.getSn());
	                            cbsEquipmentErrorLog.setIsClose(1);
	                            cbsEquipmentErrorLog.setAbnormalCause("备件序列号未通过验证");
	                            flag = false;
	                            systemService.save(cbsEquipmentErrorLog);
	                            listbySql.add("异常");
	                        }
	                        topItemSnList.add(listbySql.get(0));
	                    }
	                }
	                List<String> repairEndDateList = new ArrayList<String>();
	                List<String> list = null;
	                if (topItemSnList != null) {
	                    for (int i = 0; i < topItemSnList.size(); i++) {
	                        if (list != null) {
	                            list.clear();
	                        }
	                        list = systemService.findListbySql("select repair_End_Date from zy_equipment_spare where sn='" + topItemSnList.get(i) + "'");
	                        if (list.size() == 0) {
	                            list.add("异常");
	                        }
	                        repairEndDateList.add(list.get(0));
	                    }
	                }
	                /*获得当前时间*/
	                Date currentTime = new Date();
	                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	                String dateString = formatter.format(currentTime);
	                for (int i = 0; i < repairEndDateList.size(); i++) {
	                    List<String> listbySql1 = systemService.findListbySql("select top_item_sn from zy_equipment_spare where sn='" + s_ns[i] + "'");
	                    if (listbySql1.size() != 0 && !listbySql1.contains(cbsServiceEntity.getSn())) {
	                        if (repairEndDateList.size() != 0 && !"异常".equals(repairEndDateList.get(i)) && formatter.format(repairEndDateList.get(i)).compareTo(dateString) < 0) {
	                            cbsEquipmentErrorLog.setCbsEquipmentApplyId(hql.get(0).getId());
	                            cbsEquipmentErrorLog.setSn(cbsServiceEntity.getSn());
	                            cbsEquipmentErrorLog.setIsClose(1);
	                            cbsEquipmentErrorLog.setAbnormalCause("备件序列号未通过验证");
	                            flag = false;
	                            systemService.save(cbsEquipmentErrorLog);
	                        }
	                    }
	                }
	            }
	        }
        }
        return flag;
    }

    //序列号验证 APP
    @Override
    public boolean snValiDateApp(String cbsId, String[] s_ns) {
        boolean flag = true;
        try {
	        List<String> snList = systemService.findListbySql("select sn from zy_equipment_spare where is_spare='0';");
	        CbsServiceEntity cbsServiceEntity = systemService.getEntity(CbsServiceEntity.class, cbsId);
	       
	        //1、如果备件申请的设备序列号不存在，创建异常记录。由系统管理员创建设备序列号后关闭异常记录。
	        if (!snList.contains(cbsServiceEntity.getSn())) {
		        flag = false;
	        } else {
		        List<String> serverStateList = systemService.findListbySql("select server_state from zy_equipment_spare where sn='" + cbsServiceEntity.getSn() + "'");
		        if ("1003".equals(serverStateList.get(0))) {
			        flag = false;
		        } else {
			        //3、如果备件序列号在另外一台出保设备上，创建异常记录。由系统管理员手工验证核实后关闭异常记录。
			        List<String> topItemSnList = new ArrayList<String>();
			        List<String> listbySql = null;
			        if (s_ns != null) {
				        for (int i = 0; i < s_ns.length; i++) {
					        if (listbySql != null) {
						        listbySql.clear();
					        }
					        listbySql = systemService.findListbySql("select top_item_sn from zy_equipment_spare where sn='" + s_ns[i] + "'");
					        if (listbySql.size() == 0) {
						        flag = false;
						        listbySql.add("异常");
					        }
					        topItemSnList.add(listbySql.get(0));
				        }
			        }
			        List<String> repairEndDateList = new ArrayList<String>();
			        List<String> list = null;
			        if (topItemSnList != null) {
				        for (int i = 0; i < topItemSnList.size(); i++) {
					        if (list != null) {
						        list.clear();
					        }
					        list = systemService.findListbySql("select repair_End_Date from zy_equipment_spare where sn='" + topItemSnList.get(i) + "'");
					        if (list.size() == 0) {
						        list.add("异常");
					        }
					        repairEndDateList.add(list.get(0));
				        }
			        }
			        /*获得当前时间*/
			        Date currentTime = new Date();
			        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			        for (int i = 0; i < repairEndDateList.size(); i++) {
				        List<String> listbySql1 = systemService.findListbySql("select top_item_sn from zy_equipment_spare where sn='" + s_ns[i] + "'");
				        if (listbySql1.size() != 0 && !listbySql1.contains(cbsServiceEntity.getSn())) {
					        if (repairEndDateList.size() != 0 && !"异常".equals(repairEndDateList.get(i)) && formatter.parse(repairEndDateList.get(i)).compareTo(currentTime) < 0) {
						        flag = false;
					        }
				        }
			        }
		        }
	        }
        }catch (Exception e){
			e.printStackTrace();
        }
        return flag;
    }

    //存入故障备件
    @Override
    public void addFaultEquipment(String[] type_ids, String[] type_dess, String[] item_ids, String[] itemms, String[] s_ns, String[] equipment_ercodes, TSUser user) {
        if (type_ids != null) {
            for (int i = 0; i < type_ids.length; i++) {
                CbsFaultEquipmentEntity cbsFaultEquipmentEntity = new CbsFaultEquipmentEntity();
                cbsFaultEquipmentEntity.setTypeId(type_ids[i]);
                cbsFaultEquipmentEntity.setTypeDes(type_dess[i]);
                ZyEquipmentTypeEntity zyEquipmentType = systemService.getEntity(ZyEquipmentTypeEntity.class, type_ids[i]);
                if (!"其他".equals(zyEquipmentType.getTypeDesc())) {
                    if (item_ids != null) {
                        ZyEsItemEntity entity = systemService.getEntity(ZyEsItemEntity.class, item_ids[i]);
                        if (entity != null) {
                            cbsFaultEquipmentEntity.setItemId(item_ids[i]);
                            cbsFaultEquipmentEntity.setItem(entity.getItem() != null ? entity.getItem() : "");
                        }
                    }
                } else {
                    if (itemms != null) {
                        cbsFaultEquipmentEntity.setItem(itemms[i]);
                    }
                }
                List<CbsEquipmentApplyEntity> hql;
                if (user != null) {
                    hql = systemService.findHql("from CbsEquipmentApplyEntity where createName ='" + user.getRealName() + "' order by createDate desc ");
                } else {
                    hql = systemService.findHql("from CbsEquipmentApplyEntity order by createDate desc ");
                }
                cbsFaultEquipmentEntity.setApplyId(hql.get(0).getId());
                cbsFaultEquipmentEntity.setCode(hql.get(0).getRecordId());
                cbsFaultEquipmentEntity.setSn(s_ns[i]);
                cbsFaultEquipmentEntity.setEquipmentErcode(equipment_ercodes[i]);
                systemService.saveOrUpdate(cbsFaultEquipmentEntity);
            }
        }
    }

    /**
     * @param request
     * @param dataGrid
     * @param orgCode  组织权限code
     * @param roleCode 角色code
     * @param state    工程师状态
     * @return
     * @author xiul
     * 2016-4-18 下午8:27:18
     * Description 第三方负责人分配工程师 dataGrid列表查询
     */
    private void queryForAreaEngineerByOrgCodeAndRoleCode(HttpServletRequest request, DataGrid dataGrid, String orgCode, String roleCode, String state) {
        StringBuffer sql = new StringBuffer();
        TSUser user = ResourceUtil.getSessionUserName();

        //判断当前用户角色
        List<TSRoleUser> rUsers = findByProperty(TSRoleUser.class, "TSUser.id", user.getId());
//        ZyEngineerEntity engineer = systemService.findUniqueByProperty(ZyEngineerEntity.class, "tsBaseUser.id",user.getId());
        for (TSRoleUser ru : rUsers) {
            TSRole role = ru.getTSRole();
            if (StringUtil.isNotEmpty(role)) {
                if (!StaticValue.RULE_CODE_THIRDPARTY_HEADER.equals(role.getRoleCode())) {
                    sql.append(" SELECT ");
                    sql.append(" eg.id, eg.`name`, eg.cellphone, eg.email, eg.state,eg.authentication ");
                    sql.append(" FROM ");
                    sql.append(" zy_engineer eg ");
                    sql.append(" LEFT JOIN t_s_base_user bu on eg.ts_base_user_id = bu.ID ");
                    sql.append(" LEFT JOIN t_s_user_org uo on uo.user_id = bu.ID ");
                    sql.append(" LEFT JOIN t_s_depart dp on dp.ID = uo.org_id ");
                    sql.append(" LEFT JOIN t_s_role_user ru on ru.userid = bu.ID ");
                    sql.append(" LEFT JOIN t_s_role ro on ro.ID = ru.roleid ");
                    sql.append(" WHERE ");
                    sql.append(" eg.ts_base_user_id = '").append(user.getId()).append("' ");//区域工程师可以分配给自己任务，所以这里根据当前用户（当前登录的是区域工程师）查询出自己
                    sql.append(" AND eg.is_delete = '0' ");//只查询未逻辑删除的
                    appendSqlParameter(sql, request);
                    sql.append(" UNION ");
                }
            }
        }

        sql.append(" SELECT ");
        sql.append(" eg.id, eg.`name`, eg.cellphone, eg.email, eg.state, eg.authentication");
        sql.append(" FROM  ");
        sql.append(" zy_engineer eg ");
        sql.append(" LEFT JOIN t_s_base_user bu ON eg.ts_base_user_id = bu.ID  ");
        sql.append(" LEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID  ");
        sql.append(" LEFT JOIN t_s_depart dp ON dp.ID = uo.org_id  ");
        sql.append(" LEFT JOIN t_s_role_user ru ON ru.userid = bu.ID  ");
        sql.append(" LEFT JOIN t_s_role ro ON ro.ID = ru.roleid  ");
        sql.append(" WHERE  ");
        sql.append(" dp.org_code LIKE '").append(orgCode).append("%' ");
        sql.append(" AND dp.logic_delete = 0 ");
        sql.append(" AND ro.rolecode = '").append(roleCode).append("' ");
        sql.append(" AND eg.state = '").append(state).append("' ");
        sql.append(" AND eg.is_delete = 0 ");

        appendSqlParameter(sql, request);
        Map<String, String[]> paramsMap = request.getParameterMap();

        int totalCount = 0;
        Integer page = 1, rows = 10;
        rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
        page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        if (rows == 0 && page == 0) {
            rsMapList = findForJdbc(sql.toString());
        } else {
            dataGrid.setPage(page);
            dataGrid.setRows(rows);
            rsMapList = findForJdbc(sql.toString(), page, rows);
        }
        List<Map<String, Object>> countMapList = super.findForJdbc(sql.toString());
        if (null != countMapList && countMapList.size() > 0) {
            totalCount = countMapList.size();
        }
        dataGrid.setTotal(totalCount);
        dataGrid.setResults(rsMapList);
    }

    /**
     * @param request
     * @param dataGrid
     * @param orgCode  组织权限code
     * @param roleCode 角色code
     * @param state    工程师状态
     * @return
     * @author xiul
     * 2016-4-18 下午8:27:18
     * Description 第三方负责人分配工程师 dataGrid列表查询
     */
    private void queryForEngineerByOrgCodeAndRoleCode(HttpServletRequest request, DataGrid dataGrid, String orgCode, String roleCode, String state) {
        StringBuffer sql = new StringBuffer();
        TSUser user = ResourceUtil.getSessionUserName();

        //判断当前用户角色
        List<TSRoleUser> rUsers = findByProperty(TSRoleUser.class, "TSUser.id", user.getId());
//        ZyEngineerEntity engineer = systemService.findUniqueByProperty(ZyEngineerEntity.class, "tsBaseUser.id",user.getId());
        for (TSRoleUser ru : rUsers) {
            TSRole role = ru.getTSRole();
            if (StringUtil.isNotEmpty(role)) {
                if (!StaticValue.RULE_CODE_THIRDPARTY_HEADER.equals(role.getRoleCode())) {
                    sql.append(" SELECT ");
                    sql.append(" eg.id, eg.`name`, eg.cellphone, eg.email, eg.state,eg.authentication ");
                    sql.append(" FROM ");
                    sql.append(" zy_engineer eg ");
                    sql.append(" LEFT JOIN t_s_base_user bu on eg.ts_base_user_id = bu.ID ");
                    sql.append(" LEFT JOIN t_s_user_org uo on uo.user_id = bu.ID ");
                    sql.append(" LEFT JOIN t_s_depart dp on dp.ID = uo.org_id ");
                    sql.append(" LEFT JOIN t_s_role_user ru on ru.userid = bu.ID ");
                    sql.append(" LEFT JOIN t_s_role ro on ro.ID = ru.roleid ");
                    sql.append(" WHERE ");
                    sql.append(" eg.ts_base_user_id = '").append(user.getId()).append("' ");//区域工程师可以分配给自己任务，所以这里根据当前用户（当前登录的是区域工程师）查询出自己
                    sql.append(" AND eg.is_delete = '0' ");//只查询未逻辑删除的
                    appendSqlParameter(sql, request);
                    sql.append(" UNION ");
                }
            }
        }

        sql.append(" SELECT ");
        sql.append(" eg.id, eg.`name`, eg.cellphone, eg.email, eg.state, eg.authentication");
        sql.append(" FROM  ");
        sql.append(" zy_engineer eg ");
        sql.append(" LEFT JOIN t_s_base_user bu ON eg.ts_base_user_id = bu.ID  ");
        sql.append(" LEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID  ");
        sql.append(" LEFT JOIN t_s_depart dp ON dp.ID = uo.org_id  ");
        sql.append(" LEFT JOIN t_s_role_user ru ON ru.userid = bu.ID  ");
        sql.append(" LEFT JOIN t_s_role ro ON ro.ID = ru.roleid  ");
        sql.append(" WHERE  ");
        sql.append(" dp.org_code LIKE '").append(orgCode).append("%' ");
        sql.append(" AND dp.logic_delete = 0 ");
        sql.append(" AND ro.rolecode = '").append(roleCode).append("' ");
        sql.append(" AND eg.state = '").append(state).append("' ");
        sql.append(" AND eg.is_delete = 0 ");

        for (TSRoleUser ru : rUsers) {
            TSRole role = ru.getTSRole();
            if (StringUtil.isNotEmpty(role)) {
                //区域查工程师，查下属所有的工程师
                if (StaticValue.RULE_CODE_AREA_ENGINEER.equals(role.getRoleCode())) {
                    sql.append(" and eg.parent_id in (SELECT id from zy_engineer WHERE ts_base_user_id = '" + user.getId() + "')");
                }

                //工程师查工程师，查当前人同级下的所有工程师
                if (StaticValue.RULE_CODE_THIRDPARTY_ENGINEER.equals(role.getRoleCode())) {
                    String sqls = "SELECT parent_id FROM zy_engineer WHERE ts_base_user_id = '" + user.getId() + "'";
                    List<Map<String, Object>> l = systemService.findForJdbc(sqls);
                    if (l.size() > 0 && StringUtil.isNotEmpty(l.get(0).get("parent_id"))) { //区域工程师不为空的情况下，查找区域下的工程师
                        sql.append("AND eg.parent_id IN ( SELECT parent_id FROM zy_engineer WHERE ts_base_user_id = '" + user.getId() + "') ");
                    } else {  //区域工程师为空的情况下，查找同级下所有的工程师
                        sql.append(" and eg.belong_org = dp.id ");
                    }
                }
            }
        }

        appendSqlParameter(sql, request);
        Map<String, String[]> paramsMap = request.getParameterMap();

        int totalCount = 0;
        Integer page = 1, rows = 10;
        rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
        page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        if (rows == 0 && page == 0) {
            rsMapList = findForJdbc(sql.toString());
        } else {
            dataGrid.setPage(page);
            dataGrid.setRows(rows);
            rsMapList = findForJdbc(sql.toString(), page, rows);
        }
        List<Map<String, Object>> countMapList = super.findForJdbc(sql.toString());
        if (null != countMapList && countMapList.size() > 0) {
            totalCount = countMapList.size();
        }
        dataGrid.setTotal(totalCount);
        dataGrid.setResults(rsMapList);
    }

    /**
     * @param sql
     * @author xiul
     * 2016-4-19 下午2:38:59
     * Description 拼接查询条件 for  queryDatagridResultForAreaChooseEngineers
     */
    private void appendSqlParameter(StringBuffer sql, HttpServletRequest request) {
        Map<String, String[]> paramsMap = request.getParameterMap();
        if (StringUtil.isNotEmpty(paramsMap.get("name")) && StringUtil.isNotEmpty(paramsMap.get("name")[0])) {
            sql.append(" AND eg.name LIKE '%").append(paramsMap.get("name")[0].toString()).append("%' ");
        }
        if (StringUtil.isNotEmpty(paramsMap.get("cellphone")) && StringUtil.isNotEmpty(paramsMap.get("cellphone")[0])) {
            sql.append(" AND eg.cellphone LIKE '%").append(paramsMap.get("cellphone")[0].toString()).append("%' ");
        }
        if (StringUtil.isNotEmpty(paramsMap.get("email")) && StringUtil.isNotEmpty(paramsMap.get("email")[0])) {
            sql.append(" AND eg.email LIKE '%").append(paramsMap.get("email")[0].toString()).append("%' ");
        }

    }

    /**
     * 根据父类服务单id，查找子类
     *
     * @param ServiceId
     * @return
     */
    public List<Map<String, Object>> getParentServiceTerm(String parentCode, String ServiceId, String ParentServiceId) {
        String string = "SELECT * FROM cbs_service s WHERE s.`code` LIKE '%" + parentCode + "%' AND id != '" + ServiceId + "' AND id != '" + ParentServiceId + "'";
        return systemService.findForJdbc(string);
    }


    /**
     * 根据服务单id查找服务单类型
     *
     * @param ServiceId
     * @return
     */
    public List<Map<String, Object>> getServiceType(String ServiceId) {
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT\n");
        sf.append("\ts.`code,`\n");
        sf.append("\tt2.`name`\n");
        sf.append("FROM\n");
        sf.append("\tcbs_service s\n");
        sf.append("LEFT JOIN cbs_service_type t1 ON t1.id = s.service_type_1\n");
        sf.append("LEFT JOIN cbs_service_type t2 ON t2.id = s.service_type_2\n");
        sf.append("WHERE\n");
        sf.append("\ts.id = '").append(ServiceId).append("'");
        return systemService.findForJdbc(sf.toString());
    }

    /**
     * 替换sql中的变量
     *
     * @param sql
     * @return
     */
    public String replaceVal(String sql, CbsServiceEntity t) {
        sql = sql.replace("#{id}", String.valueOf(t.getId()));
        sql = sql.replace("#{code}", String.valueOf(t.getCode()));
        sql = sql.replace("#{zy_contacts_id}", String.valueOf(t.getZyContactsId()));
        sql = sql.replace("#{sn}", String.valueOf(t.getSn()));
        sql = sql.replace("#{zy_equipment_spare_id}", String.valueOf(t.getZyEquipmentSpareId()));
        sql = sql.replace("#{zy_cinema_id}", String.valueOf(t.getZyCinemaId()));
        sql = sql.replace("#{province}", String.valueOf(t.getProvince()));
        sql = sql.replace("#{city}", String.valueOf(t.getCity()));
        sql = sql.replace("#{address}", String.valueOf(t.getAddress()));
        sql = sql.replace("#{client}", String.valueOf(t.getClient()));
        sql = sql.replace("#{source_type}", String.valueOf(t.getSourceType()));
        sql = sql.replace("#{service_type_1}", String.valueOf(t.getServiceType1()));
        sql = sql.replace("#{service_type_2}", String.valueOf(t.getServiceType2()));
        sql = sql.replace("#{description}", String.valueOf(t.getDescription()));
        sql = sql.replace("#{judge400}", String.valueOf(t.getJudge400()));
        sql = sql.replace("#{status}", String.valueOf(t.getStatus()));
        sql = sql.replace("#{create_name}", String.valueOf(t.getCreateName()));
        sql = sql.replace("#{create_by}", String.valueOf(t.getCreateBy()));
        sql = sql.replace("#{create_date}", String.valueOf(t.getCreateDate()));
        sql = sql.replace("#{update_name}", String.valueOf(t.getUpdateName()));
        sql = sql.replace("#{update_by}", String.valueOf(t.getUpdateBy()));
        sql = sql.replace("#{update_date}", String.valueOf(t.getUpdateDate()));
        sql = sql.replace("#{UUID}", UUID.randomUUID().toString());
        return sql;
    }
}